CODEWITHSUNDEEP
sqlsqlite
thesam
thesam

Reputation: 130

SQLite - select the newest row with a certain field value

I have an SQLite question which essentially boils down to the following problem.

id | key | data
1  |   A |  x
2  |   A |  x
3  |   B |  x
4  |   B |  x
5  |   A |  x
6  |   A |  x

New data is appended to the end of the table with an auto-incremented id.

Now, I want to create a query which returns the latest row for each key, like this:

id | key | data
4  |   B |  x
6  |   A |  x

I've tried some different queries but I have been unsuccessful. How do you select only the latest rows for each "key" value in the table?

Upvotes: 1

Views: 307

Answers (4)

SF.
SF.

Reputation: 14029

Without nested SELECTs, or JOINs but only if the field determining "newest" is primary key (e.g. autoincrement):

  SELECT * FROM table GROUP BY key DESC;

Upvotes: 0

dcn
dcn

Reputation: 4469

use this SQL-Query:

select * from tbl where id in (select max(id) from tbl group by key);

Upvotes: 4

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115510

SELECT *
FROM mytable
    JOIN 
        ( SELECT MAX(id) AS maxid
          FROM mytable
          GROUP BY "key"
        ) AS grp
      ON grp.maxid = mytable.id

Side note: it's best not to use reserved words like keyas identifiers (for tables, fields. etc.)

Upvotes: 0

Jhaliya - Praveen Sharma
Jhaliya - Praveen Sharma

Reputation: 31722

You could split the main task into two subroutine.

You could move with the approach first retrieve all id/key value then get the id for the latest value of A and B keys,

Now you could easly write a query to get latest value for A and B because you have value of id's for both A and B keys.

Upvotes: 0

Related Questions