SQL Server Performance Forum – Threads Archive

Need help on select query!

How can I get top 1 (max (date_created)) record for each ID? I want to get the latest data that was modified for each different ID. Use Database
GO drop table #t1
go
create table #t1
( id int,
name varchar(25),
date_created datetime,
type varchar(20) ) insert into #t1(id, name, date_created, type)
values (1, ‘test1′, ’01-25-2007’, ‘AM’) insert into #t1(id, name, date_created, type)
values (2, ‘test2′, ’01-26-2007’, ‘PM’) insert into #t1(id, name, date_created, type)
values (1, ‘test1′, ’01-27-2007’, ‘AM’) insert into #t1(id, name, date_created, type)
values (2, ‘test2′, ’01-25-2007’, ‘PM’) insert into #t1(id, name, date_created, type)
values (1, ‘test1′, ’01-24-2007’, ‘AM’) insert into #t1(id, name, date_created, type)
values (2, ‘test2′, ’01-22-2007’, ‘PM’) GO
Desired result set: id name date_created type
———– ————————- —————————————————— ——————–
1 test1 2007-01-27 00:00:00.000 AM
2 test2 2007-01-26 00:00:00.000 RR
I couldn’t figure it out. Any help would be really appreciated Thanks, Name
———
Dilli Grg (1 row(s) affected)

---

Never mind, I got it. Sorry for the spam. Thanks, Name
———
Dilli Grg (1 row(s) affected)

---

Care to post your solution ? This is my attempt
select #t1.*
from #t1 inner join
(
selectid, max_date_created = max(date_created)
from#t1
group by id
) m
on#t1.id = m.id
and#t1.date_created= m.max_date_created
order by #t1.id KH

---

http://www.sql-server-performance.com/faq/sqlviewfaq.aspx?topicid=15&faqid=121<br />[<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />–<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

---

Hi Khtan,
Your solution works as well. Here is the simple way I did it. SELECT ID, [Name], Type, MAX(date_created) AS [Latest Date Created]
FROM #t1
GROUP BY ID, Name, Type
Frank, thanks for the link. Thanks, Name
———
Dilli Grg (1 row(s) affected)

---

You will have problem with these data. I added one more record in red. Unless this is what you wanted.
drop table #t1
go
create table #t1
( id int,
name varchar(25),
date_created datetime,
type varchar(20)
) insert into #t1(id, name, date_created, type)values (1, ‘test1′, ’01-25-2007’, ‘AM’)
insert into #t1(id, name, date_created, type)values (2, ‘test2′, ’01-26-2007’, ‘PM’)
insert into #t1(id, name, date_created, type)values (1, ‘test1′, ’01-27-2007’, ‘AM’)
insert into #t1(id, name, date_created, type)values (2, ‘test2′, ’01-25-2007’, ‘PM’)
insert into #t1(id, name, date_created, type)values (1, ‘test1′, ’01-24-2007’, ‘AM’)
insert into #t1(id, name, date_created, type)values (2, ‘test2′, ’01-22-2007’, ‘PM’)
insert into #t1(id, name, date_created, type) values (2, ‘test1′, ’01-28-2007’, ‘PM’) SELECT id, [name], type, MAX(date_created) AS [Latest Date Created]
FROM #t1
GROUP BY id, name, type /*
id name type Latest Date Created
———– ————————- ——————– ——————————————————
1 test1 AM 2007-01-27 00:00:00.000
2 test1 PM 2007-01-28 00:00:00.000
2 test2 PM 2007-01-26 00:00:00.000
*/ KH

---

Yes, the name field should also be the same but just the timestamp is different. Thanks for the suggestion though. Thanks, Name
———
Dilli Grg (1 row(s) affected)

---

]]>