May 21, 2005 at 2:29 am
I am trying to select a list of active data from a table. The table is keyed on two columns, an ID int and date column. I am looking for the latest records for each ID excluding future dates. However as some of the dates are the same as an older redundant record I cannot seem to get the correct information back. Heres an example:
ID Desc DATE
1 England 2005-04-21 10:30:02.200
1 England 2005-05-21 11:00:00.000
2 EC 2005-04-21 10:30:02.200
3 France 2005-04-21 10:30:02.200
4 Italy 2005-04-21 10:30:02.200
4 Greece 2006-01-01 00:00:00.000
The result I am looking for is:
1 England 2005-05-21 11:00:00.000
2 EC 2005-04-21 10:30:02.200
3 France 2005-04-21 10:30:02.200
4 Italy 2005-04-21 10:30:02.200
Any ideas. Thanks in advance.
Mark
May 21, 2005 at 6:16 am
Hi,
I'm not sure if this is the sort of thing your after?
select * from #sql s where date = (select max(date) from #sql where id = s.id and date !> getdate()) order by id
I'm sure there are other ways to do it but I think this works for the problem you described.
Jon
May 21, 2005 at 7:12 am
Thanks Jon,
Thats worked OK. I was missing the join back to the ID in the original table.
chuz Mark
May 21, 2005 at 12:13 pm
You can do it without a join:
SELECT DISTINCT ID, DESC, MAX(DATE) As Max_Date
FROM Table
WHERE DATE <= Getdate()
GROUP BY ID, DESC
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply