Show the user the position of the test

Navigation
0

I have a table where I store test scores. Ex.:

Name, Note1, Note2, Note3, Course

The result is this: 

Nome  | Nota1 | Nota2 | Nota3 | Curso
Pedro | 7.6   | 5.5   | 3.2   | Desenho
João  | 8.0   | 3.1   | 6.6   | Desenho
Ana   | 9.0   | 6.5   | 2.2   | Desenho

I would like to check that Ana took first, John in second and Peter in third in column Note1 of the Drawing course. This way: 

Curso Desenho
1º lugar Ana
2º lugar João
3º lugar Pedro

I understand that if I use it this way: 

SELECT *, GREATEST(Nota1, Nota2, Nota3) AS MaiorNota FROM curso = '".$curso."' ORDER BY Nota1 DESC;

Or 

 SELECT * FROM curso = '".$curso."' ORDER BY Nota1 DESC;

I can get the highest grade and put it in order from highest to lowest, but how can I show Pedro that he took 3rd place, Ana that he took 1º and so on?

    
asked by anonymous 21.12.2018 / 14:59

3 answers

1

An alternative is to create a dynamic column with the position of each student within the ordering and then select it.

Considering a simplified table: 

create table notas(
  id int not null auto_increment,
  nome varchar(255) not null,
  nota int,
  primary key (id)
);

You could do: 

set @pos := 0;

select * from (
  select *, (@pos := @pos+1) as "posição"
  from notas
  order by nota desc
) as ranking
where ranking.nome = 'pedro'

Thus, the% internal% will classify all students, generating the select column. Then you make the selection from this result by filtering by the desired name. The result would be something like: 

id  nome    nota    posição
1   pedro   76      2

Considering my test data: 

insert into notas values (default, 'pedro', 76), 
                         (default, 'joão',  60), 
                         (default, 'ana',   92);

See working in DB-Fiddle

    
21.12.2018 / 15:41
0

I believe you can do this:   select * from nome_tabela WHERE curso='Desenho' ORDER BY Nota1 DESC

In other words, the result of this select will be in descending order in relation to the Note1 field. You can also use a LIMIT 3 at the end of this query that shows only the first 3 results of the table, in case there are more records. It would look like this: select * from nome_tabela WHERE curso='Desenho' ORDER BY Nota1 DESC LIMIT 3

    
21.12.2018 / 15:16
0

Good afternoon Fox, I will bring another point of view in question to your question, as it is tagged the PHP tag I will assume you are using PHP pro processing / filtering data.

Use the MBoss select (I've created a BD test) 

SELECT * FROM tb_nota WHERE curso='desenho' ORDER BY vl_nota DESC

And in php, it is where you will generate the positions: (Positions will be saved, according to each student) 

$query = mysqli_query($connect,"select * from tb_nota WHERE nm_curso='desenho' ORDER BY vl_nota DESC;");
$posicao = 0;
while ($seq = mysqli_fetch_assoc($query)) {

    $posicao += 1;

    echo $seq['nm_aluno'] . " Posição : ".$posicao."° <br>";

}

The result would look like this:

Thedataofthebank:(RememberingthatI'mdisplayingwiththeselectdoneinDESC)

I hope I have helped !!

    
22.12.2018 / 19:52
Validation classes Laravel Start reading txt from a specific line in Python [duplicate]