Page 1 of 1
Help with Query
Posted: Sun Mar 10, 2024 8:41 pm
by 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:
I need to get count(*) in DESC. What do I do wrong?
Re: help with QWERY
Posted: Sun Mar 10, 2024 9:01 pm
by 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.
Re: Help with Query
Posted: Sun Mar 10, 2024 9:07 pm
by 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.
Re: Help with Query
Posted: Sun Mar 10, 2024 9:42 pm
by mk-soft
Re: Help with Query
Posted: Mon Mar 11, 2024 9:24 pm
by doctorized
infratec wrote: Sun Mar 10, 2024 9:07 pm
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!