Home > OS >  how do I make this query work mysql 5.5.20 two tables with rank function
how do I make this query work mysql 5.5.20 two tables with rank function

Time:11-23

I have Two Tables

Students Table

id adminno fullname
1 p001 john
2 p002 Jane
3 p003 Jack
4 p004 Joan

Marks table

id adminno term year marks
1 p001 Term I 2021 300
2 p002 Term I 2021 400
3 p003 Term I 2021 200
4 P004 Term I 2021 700

Expected Results

id adminno fullname term year marks Rank
4 p004 Joan Term I 2021 700 1
2 p002 Jane Term I 2021 400 2
1 p001 john Term I 2021 300 3
3 P003 Jack Term I 2021 200 4

my Code

   SET @curRank := 0;
   SELECT 
    students.adminno,
    students.fullname,
    students.id,
    students.adminno,
    marks.term,
    marks.year,
    marks.total as total,
    rank FROM
    (SELECT
    students.adminno,
    students.fullname,
    students.id,
    marks.adminno,
    marks.id,
    marks.term,
    marks.year,
    marks.total as total,
    @curRank := IF(@prevRank = total, @curRank, @incRank) AS rank, 
    @incRank := @incRank   1, 
    @prevRank := total
    FROM marks p, (
    SELECT @curRank :=0, @prevRank := NULL, @incRank := 1 )r
    INNER JOIN students.adminno =marks.adminno 
    WHERE students.term='Term I'
    ORDER BY total DESC ) s;

I'm getting this Error

ERROR 1248 (42000): Every derived table must have its own alias

How Do I join the two tables correctly to get my desired results. I'm a beginner in mysql

CodePudding user response:

SELECT total.*, @rank := @rank   1 AS student_rank
FROM ( SELECT *
       FROM marks 
       JOIN students USING (id, adminno) ) total
CROSS JOIN ( SELECT @rank := 0 ) variable
ORDER BY total.marks DESC;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=bc74b343be4722352c1a193bf2a709ea

  • Related