Select most recent DATE for an ID.
682388Feb 6 2009 — edited Feb 9 2009Hi All,
I need to SELECT the records with the most recent DATE for each ID. A DATE range is part of the selection criteria.
My data.
MY_ID MY_DATE
1684662 26-JAN-09
1424097 27-JAN-09
1684663 27-JAN-09
1684664 27-JAN-09
1684672 28-JAN-09
0689073 28-JAN-09
1052476 21-JAN-09
1052476 21-JAN-09
1360828 23-JAN-09
1684661 23-JAN-09
1052476 30-JAN-09
1052476 30-JAN-09
1052476 30-JAN-09
1052476 30-JAN-09
The code below works fine when selecting 1 ID in the SUBSELECT, but with multiple ID it still selects rownum=1 (of course). This as far as my thinking takes me.
SELECT my_id,
my_date
FROM
(SELECT my_id,
my_date
FROM my_table
ORDER BY my_date DESC
)
WHERE rownum = 1
AND *{color:#ff0000}my_id = 1052476{color}*
AND TO_CHAR(my_date,'YYYY/MM/DD') BETWEEN '2009/01/01' AND '2009/01/31';
If I could somehow pass the SELECT ID into the SUBSELECT WHERE clause I should have this done.
Any suggestions?
Thank You in Advance for Your help,
Lou