Help with SQLite query

Question

I'm writing an application for android and have a fairly simple SQLite3 database (see pic), but I'm having a lot of trouble with a specific query.

Here's (a part of) the database. Note: there's a one-to-many relationship here. A player can have many events but only 1 player for each event.

Table model

I want to have a list of all the players, but I need to order this list of all players by whether they have an event that has it's Diagnosed attribute set to false or not(i.e. All players with an "Undiagnosed" event go at the top, all other players come after). If they have more than one "Undiagnosed" event than the newest one is used.

That's the trickiest part of the query. After that the players with the "Undiagnosed" events need to be ordered by their 'Magnitude' attribute (int), and the other players (any player that doesn't have any "undiagnosed" events) need to be ordered by their last name.

Here's an example of the list I need:

Player <--1 Undiagnosed event (100 magnitude)
Player <--2 Undiagnosed events (Newest one 75 magnitude)
Player <--1 Undiagnosed event (50 magnitude)
Player <-- No Undiagnosed Events (Name: Johnny Appleseed)
Player <-- No Undiagnosed Events (Name: Jim Zebra)

The only way I can think of doing this is by using 2 separate queries (one for "Undiagnosed" players and another for everyone else) but I don't think that's the best way of doing this.

Thanks in advance!

Edit

Ok so here's the query that's mostly working now.

select player.PlayerID, player.fname, player.lname, stats.diagcount, topmag.magnitude 
from player left outer join (
select playerid, MIN(diagnosed) as diagcount
from events group by playerid
)as stats on player.playerid = stats.playerid
left outer join (
select playerid,max(magnitude) as magnitude
from events group by playerid
)as topmag on player.playerid=topmag.playerid
order by CASE WHEN stats.diagcount Is NULL Then 1 Else 0 End,stats.diagcount,topmag.magnitude,lname;

Only problem now is that players at the bottom of the list (Players without any diagnosed events) are being sorted by their most recent event magnitude and not by last name.

"The only way I can think of doing this is by using 2 separate queries (one for "Undiagnosed" players and another for everyone else) but I don't think that's the best way of doing this." Don't assume that a query consisting of inline views and unions is somehow flawed from the get-go or would perform poorly--the optimizer can sometimes have an easier time with such queries, and they're certainly more legible and maintainable because the discrete subsets are more clearly represented. — Tim
– Tim, Commented Sep 14, 2011 at 11:38

score 1 · Accepted Answer · 2011-09-14 08:36:00Z

1

something like:

select player.fname, player.lname, stats.diagcount, topmag.highest
from player left outer join (
     select playerid, count(time) as diagcount
     from events group by playerid
)as stats on player.playerid = stats.playerid
left outer join (
   select playerid,max(magnitude) as highest
    from events group by playerid
)as topmag on player.playerid=topmag.playerid
order by stats.diagcount,topmag.highest,lname

dunno whats possible in sqllite but i think something like that might work, if you get errors post them and I will see what I can do

If you want to eliminate sorting by magnitude on player whos events are diagnosed then you could eliminate them from the sub queries..

select player.fname, player.lname, stats.diagcount, topmag.highest
from player left outer join (
     select playerid, count(time) as diagcount
     from events 
     where diagnosed = 0
     group by playerid
)as stats on player.playerid = stats.playerid
left outer join (
   select playerid,max(magnitude) as highest
    from events 
    where diagnosed = 0
    group by playerid
)as topmag on player.playerid=topmag.playerid
order by stats.diagcount,topmag.highest,lname

sorry I might have the diagnosed thing the wrong way round (as in maybe it should be diagnosed = 1)

edited Sep 14, 2011 at 8:36

answered Sep 13, 2011 at 15:26

user359135

Sign up to request clarification or add additional context in comments.

2 Comments

javajavajavajavajava Over a year ago

Hey @Gordatron. Thanks for the help! We did some tweaking on your query (which is very good and got us on the right track!) and it's working very well. I'll post the updated code in the question. There's just one problem... A player who has been diagnosed is also sorted by their magnitude (if they HAD an event) and not by their last name. Any way to do if diagnosed==1 then order by lname? I'll still mark this as the answer, thanks!

user359135 Over a year ago

updated the answer with one possible option.. it will not return any information about diagnosed events

Collectives™ on Stack Overflow

Help with SQLite query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related