B4J Question SQLite help needed

AVRC

New Member
Licensed User
Longtime User
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
 

tchart

Well-Known Member
Licensed User
Longtime User
You should be able to do this using the group by function.

Assume tables are called people and results

B4X:
select p.ID, r.level, max(r.score) -- we need an aggregate operator for score as this isnt included in the group by statement
from people p, result r -- suffix your table with a letter for more compact queries
where p.ID = r.guestid --this is the inner join
group by p.ID, r.level --these are the fields we are grouping by, these dont need an aggregate operator
order by max(r.score) LIMIT 100 --lastly sort by score descending

Basically you need to define the columns you want to group by, for others you need to use an operator such as min, max, average etc.

See here; https://www.w3schools.com/sql/sql_groupby.asp

PS Ive used the old Oracle syntax for the join as I find this easier to read (https://www.techonthenet.com/oracle/joins.php)
 
Upvote 0

Didier9

Well-Known Member
Licensed User
Longtime User
Hi all,
Does a list always maintain its order if I just add items in a loop?
From the SQLite web site (https://www.sqlite.org/queryplanner.html):
SQLite usually creates rowids beginning with one and increasing by one with each added row. But if rows are deleted, gaps can appear in the sequence. And the application can control the rowid assigned if desired, so that rows are not necessarily inserted at the bottom. But regardless of what happens, the rowids are always unique and in strictly ascending order.
 
Upvote 0

AVRC

New Member
Licensed User
Longtime User
Thank you tchart
I am still trying to figure out why it works, but it does.
I had duplicate people first try, but it seems perfect now after removing r.level from the GROUP BY section.

B4X:
SELECT p.id, p.nick, p.rep, r.level, MAX(r.seconds)  -- I put in the other columns I needed
FROM people p, results r 
WHERE p.ID = r.guestid 
GROUP BY p.ID 
ORDER BY level DESC, seconds DESC 
LIMIT 100;

I need to fill the DB further and recheck, but it looks like this is solved. Thank you again.

Didier-
I should have been more clear, I meant B4J List.
I think I remember Erel mentioned it in a video but I can't find it.
For now thankfully, I don't need that answer.
 
Upvote 0

Didier9

Well-Known Member
Licensed User
Longtime User
Sorry, that's what happens when I answer the mail too late...
Yes, a list maintains its order when you .Add() stuff to it.
That's kind of the whole point even though it can be sorted and you can add/delete elements anywhere in a list.
 
Upvote 0
Top