I have a query:
select
m.x1,
round (to_date (l.y1, 'mm/dd/yyyy HH:MI:SS AM') - m.x2, 0) as numofdays
from
table1 m,
table2 l
where
l.x3 = m.x3 and
to_date (l.y1, 'mm/dd/yyyy HH:MI:SS AM') >= TO_DATE('01012013','MMDDYYYY') and
...;
and I got this result table:
| x1 (ID) | numofdays |
|---|
| 001 | 5 |
001 | 10 |
| 002 | 2 |
| 003 | 3 |
| 003 | 1 |
| 004 | 0 |
| 005 | 66 |
| ... | ... |
several ID's have multiple values on the second column, I want to have only distinct IDs with smallest "numofdays" like this:
| x1 (ID) | numofdays |
|---|
| 001 | 5 |
| 002 | 2 |
| 003 | 1 |
| 004 | 0 |
| 005 | 66 |
| ... | ... |
Any ideas?