how do I make this query work mysql 5.5.20 two tables with rank function-CodePudding

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