Max(Date) on two dates that are identical

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Max(Date) on two dates that are identical

Author

Topic

tandreasen
Starting Member

5 Posts

Posted - 2010-11-02 : 10:55:45

Hey all,

I was wondering if there is a way to select a distinct record by date even if dates are identical. I know that Max(date) won't work if two records have the exact same date: with my query, it will always choose both records: i.e.

ID ForeignId CreatedDate
1 14E78D9B-A75B-4E89-B85E-7D9F82389D9B 2010-10-18 21:20:10.423
2 14E78D9B-A75B-4E89-B85E-7D9F82389D9B 2010-10-18 21:20:10.423

I have a query which is selecting ForeignId, MAX(CreatedDate), etc.
so it should only take the latest record, but there is a remote possibility that this can occur. Is there anything I can do to ensure that it still takes just one of the above records (even if its random, I don't care, I can't have it returning 2 records).

Thanks all,
T. Andreasen

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-02 : 11:41:48

Here's one way:


select t.id
	,t.foreignid
	,t.createdDate
from	MyTable t
cross apply (
	select top 1 id
	from	myTable
	where foreignid = t.foreignid
	order by createdDate desc
		,id desc
	) ca
where	t.id = ca.id

Be One with the Optimizer
TG

tandreasen
Starting Member

5 Posts

Posted - 2010-11-02 : 13:00:48

That works perfectly, thank you!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-11-02 : 13:05:56

You're welcome!

Be One with the Optimizer
TG

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-11-03 : 02:32:04

Hi another approach using partition

select F.*
from
(
select id,foreignid,createddate,[RowId]=ROW_NUMBER() over (partition by foreignid,createddate order by id) from Foreignkeys
)F
where F.RowId =1

Iam a slow walker but i never walk back

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources