I know I can do this with a CTE or another means but I am wondering if this can be done in one select query (no sub-selects). I want to find the most recent crt_ts for the po_nbr and then take the associated id.
Note:
The id column is not guaranteed to be sequential, I simplified it for this question.
Code:
create table temp
(
id int,
po_nbr int,
crt_ts datetime
)
insert into temp values (20, 100, '09/01/2009')
insert into temp values (3, 100, '09/03/2009')
insert into temp values (5, 100, '09/05/2009')
insert into temp values (6, 100, '09/07/2009')
insert into temp values (4, 200, '08/01/2009')
insert into temp values (29, 200, '08/03/2009')
insert into temp values (12, 200, '08/05/2009')
insert into temp values (18, 200, '08/07/2009')
Desired result:
id po_nbr
---------
6 100
18 200