Hi all,
Long time listener, first time caller.
I can usually find my way through issues reading, but am completely stuck now.
I have a SQLite database with multiple tables.
One contains people with their attributes and another contains test results for individual sessions with 4 columns (Primary ID, peopleid, level and score).
I want the top 100 results, containing ID, name, and a few columns from the people table, with their results, sorted by level and score, with no people duplicated.
Basically everyone's single best result sorted.
This gives me the ID from the people column correctly:
SELECT DISTINCT people.rowid FROM results INNER JOIN people ON people.ID = results.guestid ORDER BY level DESC, seconds DESC LIMIT 100;
This gives me the data in the rows that I need correctly, but without the distinct people:
SELECT nick, rep, level, seconds FROM results INNER JOIN people ON people.ID = results.guestid ORDER BY level DESC, seconds DESC LIMIT 100;
Can what I need be done in SQLite or do I need to do this with multiple queries and combine it with my code?
If not, follow up question- Does a list always maintain its order if I just add items in a loop?
Thanks,
Craig
Long time listener, first time caller.
I can usually find my way through issues reading, but am completely stuck now.
I have a SQLite database with multiple tables.
One contains people with their attributes and another contains test results for individual sessions with 4 columns (Primary ID, peopleid, level and score).
I want the top 100 results, containing ID, name, and a few columns from the people table, with their results, sorted by level and score, with no people duplicated.
Basically everyone's single best result sorted.
This gives me the ID from the people column correctly:
SELECT DISTINCT people.rowid FROM results INNER JOIN people ON people.ID = results.guestid ORDER BY level DESC, seconds DESC LIMIT 100;
This gives me the data in the rows that I need correctly, but without the distinct people:
SELECT nick, rep, level, seconds FROM results INNER JOIN people ON people.ID = results.guestid ORDER BY level DESC, seconds DESC LIMIT 100;
Can what I need be done in SQLite or do I need to do this with multiple queries and combine it with my code?
If not, follow up question- Does a list always maintain its order if I just add items in a loop?
Thanks,
Craig