Return all record for specific value for which another column value is not max

Question

I have the below input data set

nbr,id,qty1,qty2,qty3,qty4,process_timestamp
'1','abc',20,21,22,23,'12/14/2016 9:59'
'1','bcd',20,21,22,23,'12/15/2016 9:59'
'1','bcde',20,21,22,23,'12/13/2016 9:59'
'2','dabc',20,21,22,23,'12/15/2016 9:59'
'2','dabcd',20,21,22,23,'12/16/2016 9:59'

and i want the result as below

nbr,id,qty1,qty2,qty3,qty4,process_timestamp
'1','bcde',20,21,22,23,'12/13/2016 9:59'
'1','abc',20,21,22,23,'12/14/2016 9:59'
'2','dabc',20,21,22,23,'12/15/2016 9:59'

I want to get all records based on field nbr and process_timestamp , where the process_timestamp is less than MAX(process_timestamp) for each nbr.

This is what I have tried so far.

Select nbr,id,qty1,qty2,qty3,qty4,process_timestamp from 
check where  process_timestamp NOT IN (SELECT MAX(process_timestamp) from check group by nbr) group by nbr,id,qty1,qty2,qty3,qty4,process_timestamp;

But result(below) what i am getting is not correct

1       abc     20.0    21.0    22.0    23.0    12/14/2016 9:59
1       bcde    20.0    21.0    22.0    23.0    12/13/2016 9:59

Can anyone please correct me as where I am going wrong.

Answer 1

Try this:

Select nbr,id,qty1,qty2,qty3,qty4,process_timestamp
from check c1
where process_timestamp <> (
    SELECT MAX(process_timestamp)
    from check c2
    where c1.nbr = c2.nbr);

Answer 2

In standard SQL, you can use a correlated subquery for this:

select c.*
from check c
where process_timestamp < (select max(c2.process_timestamp)
                           from check c2 
                           where c2.nbr = c.nbr
                          ) ;