CODEWITHSUNDEEP
sqlsqlite
Horhc
Horhc

Reputation: 25

How to get last records with conditions from SQLite?

I need to get last records from SQLite but with some conditions.. Let's say I have this table 

id         course
1           math 
2           english      
3           math       
4           english   
5           chemistry

and I need to return records of ids 3,4,5 because for math the id: 3 is the last(newest) record, for english the id: 4 is the last(newest) record and so on..

I don't know how to group them and then pick the newest record and so on..

Upvotes: 0

Views: 590

Answers (2)

shA.t
shA.t

Reputation: 16978

SQLite has a rowid that you can query over it like this:

select *   
from Table1 t
where exists (
  select 1
  from Table1 ti  
  where t.course = ti.course
  group by ti.course
  having t.rowid = max(ti.rowid);    -- filtering rows that are newest rows in table
  );

SQLite Fiddle Demo

Sample Data:

| id |    course |      ... hidden rowid |
|----+-----------|      ... -------------|
|  1 |      math |      ...            1 |
|  2 |   english |      ...            2 |
|  3 |      math |      ...            3 |
|  4 |   english |      ...            4 |
|  5 | chemistry |      ...            5 |
|  8 |      test |      ...            6 |
|  7 |      test |      ...            7 |

Result:

| id |    course |      ... hidden rowid |
|----+-----------|      ... -------------|
|  3 |      math |      ...            3 |
|  4 |   english |      ...            4 |
|  5 | chemistry |      ...            5 |
|  7 |      test |      ...            7 |

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You seem to want:

select max(id) as id, course
from t
group by course;

Upvotes: 2

Related Questions