CODEWITHSUNDEEP
sqldjangosqlite
user972014
user972014

Reputation: 3856

SQL - Query latest value from table for each match

I have 2 tables, similar to this:

Person:
    id
    name

HeightStatus:
    timestamp  // The time that this status is relevant to
    person_id
    height

Possible values can be

Person - [1, John], [2, Bob], [3, Jeniffer]

HeightStatus - [100, 1, 5], [150, 1, 7], [40, 2, 12], [30, 2, 9], [400, 3, 7]

This means that I have 3 persons, and on time 100, john's height was 5, at time 150 his height was 7 and so on

querying the latest height of a specific person is easy (for example for the person with id 1):

select height
from Person, HeightStatus
where Person.id==HeightStatus.person_id and Person.id == 1
order by timestamp desc
limit 1

My problem is how to use that as part of a larger query?

e.g - I want to get all the people that their latest height is greater than 8

I guess it is possible to use a view or just use a query within a query.

I'm using Django, but I'm open to writing that as a plain SQL query and necessarily using Django's ORM

Upvotes: 0

Views: 60

Answers (2)

Shawn
Shawn

Reputation: 52409

If you're using Sqlite 3.25 or newer, window functions allow for an elegant solution:

WITH bytimes AS (SELECT id, name, timestamp, height
                      , rank() OVER (PARTITION BY id ORDER BY timestamp DESC) AS rank
                 FROM person JOIN heightstatus ON id = person_id)
SELECT * FROM bytimes WHERE rank = 1 AND height > 8;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I would just use a correlated subquery:

select hs.*
from heightstatus hs
where hs.timestamp = (select max(hs2.timestamp) from heightstatus hs2 where hs2.person_id = hs.person_id);

Upvotes: 1

Related Questions