CODEWITHSUNDEEP
sqlpostgresql
aleroot
aleroot

Reputation: 72636

SQL Select rows that have higher timestamp value

On a PostgreSQL Server i've a table like this below : 

+------+------+------------------+
| Code |  kind|    timestamp     |
+------+------+------------------+
|  1   |  I   | 16-05-2011 09:17 | 
|  1   |  O   | 16-05-2011 09:47 | 
|  2   |  I   | 16-05-2011 09:37 | 
|  3   |  I   | 16-05-2011 11:26 | 
|  3   |  O   | 16-05-2011 12:11 |
|  3   |  I   | 16-05-2011 13:23 |
+------+------+------------------+

This table represent IN and OUT of person in a pool with relative timestamp, and i want to know the person in the pool at a given moment.

Is there a way to retrieve The row of kind 'I' that not have a corresponding 'O' ? can i do it with a single Query ?

The code represent the person code ...

Upvotes: 3

Views: 239

Answers (2)

Conrad Frix
Conrad Frix

Reputation: 52655

LEFT JOIN is one way to do it with

SELECT 
       t.Code,
       t.kind,
       t.timestamp 
FROM   table t 
       LEFT JOIN table t2 
         ON t.code = t2.code 
            AND t.kind = 'I' 
            AND t2.kind = 'O' 
WHERE  t2.code IS NULL

It should be doable with Not Exists as well

Upvotes: 5

csl
csl

Reputation: 11358

Assuming Code is a person, you could convert kind I and O to numbers +1 and -1, then select SUM(Code*kind), grouped by Code, having the sum being nonzero.

Upvotes: 0

Related Questions