how to get only latest date and other columns from table

Question

how to get only latest date and other columns from table

shyam.oec

Old Hand

Points: 380
More actions
April 14, 2010 at 6:42 pm

#217875

Hi,
Table
id name date
123 a 2/3/2010
124 b 15/2/2010
125 c 1/1/2010
123 a 12/4/2010
125 c 17/2/2010
this is a table, here id,name,date is composite key.
I want to select id,name and date so that latest date (e.g., for id 123 date 12/4/2010 be selected) is selected for each id and each id and name should be selected once.
So what will be its query?
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

duplicate post. Please direct all replies to the other thread at http://www.sqlservercentral.com/Forums/Topic903697-149-1.aspx

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Koncentrix Old Hand Points: 338 More actions · Answer 2

Please check this sample...

create table #temp (num int, cname varchar(50), dt smalldatetime)

insert #temp select 123, 'a', '2-3-2010 12:35:29'

insert #temp select 123, 'a', '12-4-2010 12:35:29'

select num, cname, max(dt) from #temp group by num, cname

drop table #temp

It grabs the max date which is the latest.

Hope this help expand the logic...

Koncentrix