SQL question (using MAX in SQLite)
I wasn't sure how to sum this up with a better thread title, sorry.
I have am SQLite table like this:
id(int, primary key), player(string), time(int), type(int)
And data like this:
1|Kylotan|1099864623|1
2|Kylotan|1099864676|2
3|Kylotan|1099864824|1
4|Kylotan|1099866320|2
5|Karalynne|1099872012|1
6|Karalynne|1099872222|2
Now, I need to extract rows 4 and 6, on the basis that they are the entries for each unique player that have the highest time values. "SELECT id, MAX(time), player, type FROM logins GROUP BY player;" doesn't work because it doesn't pick the id that necessarily matches the MAX(time) row.
I'm sure this should be pretty simple but I can't work it out.
Here's the idea:
SELECT id, player, time from logins l1 where l1.time = (SELECT MAX(l2.time) FROM logins l2 WHERE l1.player = l2.player );
but it may be easier to perform a query for each player.
SELECT id, player, time from logins l1 where l1.time = (SELECT MAX(l2.time) FROM logins l2 WHERE l1.player = l2.player );
but it may be easier to perform a query for each player.
Yeah, that's based on or very similar to the example given in the MySQL documentation. Doesn't work on SQLite, sadly: "SQL error: no such column: l1.player".
I'd rather not perform a query per player as there will end up with thousands of players eventually. Either way, I'd like to see if it's possible without separate queries.
I'd rather not perform a query per player as there will end up with thousands of players eventually. Either way, I'd like to see if it's possible without separate queries.
Ok... given that the subquery approach looks impossible in SQLite, how would I do this using only 1 query per player? (Say I executed a 'select distinct player from logins;' beforehand to get the list.) I still don't know how to easily pick out the id for the row that contains the max(time) (or indeed, max(id), since the two are largely equivalent here).
SELECT MAX(t1.time) WHERE t1.player = 'Kylotan'
?
see http://www.sqlite.org/omitted.html for what SQLite omits.
?
see http://www.sqlite.org/omitted.html for what SQLite omits.
The problem is, I don't just want the max(time), I need the entire row where it found that max(time). Unfortunately it's looking like I'm going to have to execute 2 queries per player to achieve this, for perhaps hundreds of players. Annoying.
I know about the omissions page, by the way.
I know about the omissions page, by the way.
Here's another idea.
Have a seperate table with just the player names and latest time. When you need to get the info with the latest time, you do a join of the two tables. It shouldn't be too bad if you have the current table indexed by time. You'd just have to update the new table whenever you add a row to the current table.
Have a seperate table with just the player names and latest time. When you need to get the info with the latest time, you do a join of the two tables. It shouldn't be too bad if you have the current table indexed by time. You'd just have to update the new table whenever you add a row to the current table.
By the way, that's a bug in SQLite, as it should give you the correct aggregate id number based on MAX(time)
I'm going to go with the secondary table option to allow the join. I can just get rid of it later if SQLite gets upgraded to allow backreferences in subqueries. Thanks for the ideas.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement