CODEWITHSUNDEEP
sqlsqlite
logoff
logoff

Reputation: 3446

Select unique rows of foreign key and last timestamp

I have a database with a table with these schema:

CREATE TABLE register (
    register_id INTEGER NOT NULL, 
    mould_id VARCHAR, 
    timestamp INTEGER NOT NULL, 
    PRIMARY KEY (register_id), 
    FOREIGN KEY(mould_id) REFERENCES mould (mould_id)
)

Timestamp is incremental integer, so bigger timestamp means more recent time.

I want to get last register (more recent/bigger timestamp) for each mould_id. Is it possible to get this using an SQL query?

Upvotes: 2

Views: 139

Answers (4)

Jesús Muñoz
Jesús Muñoz

Reputation: 48

You can group by mould_id:

select *, max(timestamp) from register group by mould_id

Upvotes: 3

Kobi
Kobi

Reputation: 2524

http://sqlfiddle.com/#!7/3ff366/6
First you can group data by mould_id and select the last timestamp (max).
Then select data from the register table where mould_id and timestamp correspond

SELECT *
  FROM register r
  JOIN ( SELECT mould_id
              , MAX(timestamp) timestamp
           FROM register
          GROUP BY mould_id ) d ON r.mould_id  = d.mould_id
                               AND r.timestamp = d.timestamp

Upvotes: 1

AnC
AnC

Reputation: 651

I'm not sure if SQL Lite does support that syntax but in postgreSQL you would do:

SELECT DISTINCT ON (r.mould_id) * FROM register r ORDER BY r.mould_id, timestamp ASC

Upvotes: 0

Mr.Bakaop
Mr.Bakaop

Reputation: 11

SELECT * FROM register WHERE mould_id = :mould_id ORDER_BY timestamp ASC LIMIT 1

Upvotes: 1

Related Questions