CODEWITHSUNDEEP
sqlsqlite
Zeppern
Zeppern

Reputation: 3

How to find the last occurring set of values for an ID in a table with sqlite?

I have the following table:

ID | time  | value
---+-------+------
 1 | 13:00 |  A
 1 | 13:00 |  B
 2 | 13:00 |  B
 2 | 13:00 |  C
 1 | 14:00 |  A
 1 | 14:00 |  C
 2 | 15:00 |  B
 2 | 15:00 |  C

And I'd like to find the last occurring values to an ID

For ID = 1, the last occurring values would be A and C.

As you can see, the time for the last occurring values for each ID is different. And the actual time is supposed to be unknown as new occurring values can come at any time.

I have tried with a following query:

SELECT value 
FROM table 
WHERE time = max(time) AND ID = 1 
GROUP BY ID, value;

But the max aggregate function cannot be used in such a way.

A workaround for my part could be to create a view like this:

CREATE VIEW view AS
SELECT ID, value, max(time) 
FROM table 
GROUP by ID, value;

With which I could just:

SELECT value 
FROM view 
WHERE ID = 1;

However I am unhappy with that solution as surely there should be an easier way through a single SELECT sentence.

Upvotes: 0

Views: 34

Answers (1)

Daniel Barral
Daniel Barral

Reputation: 4096

You can solve this with a subquery:

SELECT value FROM table

WHERE ID = 1

AND time = ( SELECT max(time) FROM table WHERE ID = 1)

Upvotes: 1

Related Questions