Jump to content
  • Advertisement
Sign in to follow this  
Kylotan

SQL question (using MAX in SQLite)

This topic is 4993 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
Advertisement
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
SELECT MAX(t1.time) WHERE t1.player = 'Kylotan'

?

see http://www.sqlite.org/omitted.html for what SQLite omits.

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
By the way, that's a bug in SQLite, as it should give you the correct aggregate id number based on MAX(time)

Share this post


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

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!