Sign in to follow this  

SQL question (using MAX in SQLite)

This topic is 4778 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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).

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

This topic is 4778 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this