1. *** New version of B4J is available ***
    B4J v7.8
    Dismiss Notice

B4J Question SQLite help needed

Discussion in 'B4J Questions' started by AVRC, Jul 1, 2019.

  1. AVRC

    AVRC New Member Licensed 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
     
  2. tchart

    tchart Active Member Licensed User

    You should be able to do this using the group by function.

    Assume tables are called people and results

    Code:
    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)
     
    OliverA likes this.
  3. Didier9

    Didier9 Active Member Licensed User

    From the SQLite web site (https://www.sqlite.org/queryplanner.html):
     
  4. AVRC

    AVRC New Member Licensed 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.

    Code:
    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.
     
  5. Didier9

    Didier9 Active Member Licensed 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.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice