Select most recent rows that match a condition in SQLite

Question

Let's say I have a table:

Name, status, timestamp

And I want to select the rows that match status='active' but only those that have the most recent timestamp for each of them. So if there were rows like this:

Bob, active, 5/10/2010
Bob, active, 6/12/2010
Ann, inactive, 6/12/2000
Ann, active, 9/3/2009
Ann, active, 9/25/2010

I'd want it to return:

Bob, active, 6/12/2010
Ann, active, 9/25/2010

How can I do this? I'm using SQLite, if it matters.

Thank you.

Answer 1

Thanks, OMG Ponies, but when I use that it gives me this error:

no such column: y.name

Answer 2

select name, status, max(timestamp) 
from my_table 
where status = 'active' 
group by name, status

take a look at http://www.w3schools.com/sql/sql_groupby.asp

Answer 3

Thank you Bill Karwin, but, wading through a number of answers that might work has been disappointing, since I keep getting results that return multiple rows of the same user. I'll keep trying, but it'd be great if someone could provide an answer that definitely works in SQLite for this particular need.

Answer 4

Thank you, Daniel Luyo, but that returns more than one row for the same user. I only want one row--the latest--for each user.

Answer 5

Here's how I solve this type of problem. You want one row for each name such that no other row exists with the same name and a greater timestamp:

SELECT t1.*
FROM MyTable t1
LEFT OUTER JOIN MyTable t2 ON t1.name = t2.name 
  AND t1.timestamp < t2.timestamp
WHERE t2.name IS NULL

But this can still return multiple rows for each name, because you could have more than one row for a given name with the same max timestamp. So use the primary key as a tie-breaker:

SELECT t1.*
FROM MyTable t1
LEFT OUTER JOIN MyTable t2 ON t1.name = t2.name 
  AND (t1.timestamp < t2.timestamp OR t1.timestamp = t2.timestamp AND t1.id < t2.id)
WHERE t2.name IS NULL

I'm assuming id is a primary key for this example, but any other unique column that increases in value chronologically would work.

Answer 6

Thank you Bill, but what about matching to the active_status? And, actually, in the real app, I will want it to match to active_status = "suspended" or active_status = "completed". I'm not sure how to work that condition into your example.

I don't have a primary id but I can add one if it is needed. Thank you again.

Answer 7

Bill, I believe I have it working now (seems to pick the right rows on different test data). I used your query and added the condition t1.active_status!='active', since that will take care of anything inactive. The whole query looks like:

SELECT t1.*
FROM TrialTypes t1
LEFT OUTER JOIN TrialTypes t2 ON t1.name = t2.name
AND (t1.start_date < t2.start_date OR t1.start_date = t2.start_date AND t1.rowid < t2.rowid)
WHERE t2.name IS NULL and t1.active_status != 'active'

Thank you very much for the help. Obviously, I'm new to more than basic SQL queries. This helps teach me, thanks!