CODEWITHSUNDEEP
sqldb2
Apache
Apache

Reputation: 1806

sql select statement by choosing min timestamp value

i'm having data as below in table

NUM_SER           STATUS     TIMESTAMP             
----------------  ---------  --------------------- 
00000000000000d1  02         2/21/2012 12:24:13 PM 
00000000000000d1  05         2/21/2012 5:49:46 PM  
00000000000000d2  01         2/21/2012 11:05:36 AM  
00000000000000d2  05         2/21/2012 10:27:57 PM  
00000000000000d2  03         2/21/2012 4:55:13 PM  
00000000000000d3  02         2/21/2012 3:18:23 PM  
00000000000000d3  05         2/21/2012 7:02:09 PM  
00000000000000d3  02         2/21/2012 7:16:03 PM

I want select query statement which will select data for each serial number with min timestamp value for selected date,

so the expected result when selection date is 2012-02-21

NUM_SER           STATUS     TIMESTAMP             
----------------  ---------  --------------------- 
00000000000000d1  02         2/21/2012 12:24:13 PM     
00000000000000d2  01         2/21/2012 11:05:36 AM   
00000000000000d3  02         2/21/2012 3:18:23 PM

how this can be done, i try to use min(TIMESTAMP) but get err 'An unexpected token "END-OF-STATEMENT"'

Upvotes: 1

Views: 2232

Answers (1)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Try this:

select * from t t1
left join t t2
on t1.num_ser = t2.num_ser and t1.timestamp > t2.timestamp
where t2.timestamp is null

Upvotes: 3

Related Questions