Help with Query

[doctorized]

Code: Select all

CREATE TABLE Lendings (book_id TEXT, stud_id TEXT, LDate DATE, Returned TINYINT, RDate DATE)
CREATE TABLE Students (id TEXT PRIMARY KEY, Lname TEXT, Fname TEXT, Class TEXT)

when I run:

Code: Select all

Select * from (select id, Lname, Fname, Class from students where id like '2023-2024%' and class = 'A2') S left join 
(select book_id, stud_id, count(book_id) from Lendings where stud_id like '2023-2024%' group by stud_id) L on L.stud_id = S.id GROUP by S.id order by count(L.book_id) DESC

I get all the rows I need but not in the right order. count(*) is like:

Code: Select all

7
6
1
1
1
2
5
...

I need to get count(*) in DESC. What do I do wrong?

[DarkDragon]

What database system are you using?

Did you try referring to the count column by name?

Code: Select all

Select * from (select id, Lname, Fname, Class from students where id like '2023-2024%' and class = 'A2') S left join 
(select book_id, stud_id, count(book_id) as cnt from Lendings where stud_id like '2023-2024%' group by stud_id) L on L.stud_id = S.id GROUP by S.id order by L.cnt DESC

Otherwise count in ORDER BY will always be 1, as you've grouped already beforehand.

[infratec]

No working code... no help.

Is it so hard to create a code which demonstrate this behavior?
Why should 10 people do this to help you?

You spent, maybe, 15 minutes.
If 10 people tries to help you and write code, it needs 150 minutes.

This is not fair.

It costs less time for the helpers to modify your code to show you a solution.

[mk-soft]

Why you start a new thread ?

Link: https://www.purebasic.fr/english/viewtopic.php?t=83756

[doctorized]

No working code... no help.

I understand that I forgot to mention it is an SQLite db.I have problem with the query so I don't think that a working code is needed. I just posted the query that returns data in wrong order. I apologize for any inconvenience. After all, DarkDragon managed to find the solution! Thanks a lot DarkDragon!