Show the user his test position

Question

I have a table where you store test notes. Ex.:

Name, Nota1, Nota2, Nota3, 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 verify that Ana took first, João in second and Pedro in third in the column Nota1 of the Drawing course. Thus:

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

I understand if I use it that 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 take 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 she took 1st and so on?

Answer 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 select internal classify all students, generating the column posição. 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

That considering my test data:

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

^{See working on DB-Fiddle}

Answer 2

I believe you can do it like 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 relative to the Nota1 field. You can also use at the end of this query a LIMIT 3 that will show only the first 3 results of the table, in case there are more records. Would look like this: select * from nome_tabela WHERE curso='Desenho' ORDER BY Nota1 DESC LIMIT 3

Answer 3

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

Use the select of Mboss (I created a test comic)

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

And in php, that’s where you’ll 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 be like this:

The data of the bank: (Remembering that I am displaying with the select done in DESC)

I hope I’ve helped !!