Select most recent rows that match a condition in SQLite
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
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 t1LEFT OUTER JOIN MyTable t2 ON t1.name = t2.name AND t1.timestamp < t2.timestampWHERE 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 t1LEFT 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.
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.*<br>FROM TrialTypes t1<br>LEFT OUTER JOIN TrialTypes t2 ON t1.name = t2.name <br> AND (t1.start_date < t2.start_date OR t1.start_date = t2.start_date AND t1.rowid < t2.rowid)<br>WHERE t2.name IS NULL and t1.active_status != 'active'<br>
Thank you very much for the help. Obviously, I'm new to more than basic SQL queries. This helps teach me, thanks!