Geek asks and answers

SQL Select rows with a higher timestamp value

On PostgreSQL Server, I have a table as shown 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 shows the IN and OUT of a person in the pool with a relative timestamp, and I want to know the person in the pool at the moment.

Is there a way to get a string like "I" that doesn't have a corresponding "O"? can i do this with a single request?

The code represents the code of the person ...

+3
source share
2 answers

LEFT JOIN is one way to do this 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 also be run with Not Exists.

+5
source

, Code , kind O +1 -1, SUM ( * ), Code, .

0

More articles:


All Articles