bkmsmith (5/6/2014)
Here is another option.
create table abc_test
(
id int
,runs int
,date1 datetime
)
;
insert into abc_test
(
id
,runs
,date1
) values
('24','1','2013-12-05 10:00:01.000'),
('24','2','2013-12-05 10:00:02.000'),
('25','1','2013-12-05 10:00:03.000'),
('25','2','2013-12-05 10:00:04.000'),
('25','3','2013-12-05 10:00:06.000'),
('26','1','2013-12-05 10:00:07.000'),
('26','2','2013-12-05 10:00:08.000'),
('27','1','2013-12-05 10:00:09.000')
;
SELECTT2.id
, [runs] = T2.MaxRuns
, T1.date1
FROMdbo.abc_test T1
INNER JOIN
(
SELECTid
, [MaxRuns] = MAX(runs)
FROMdbo.abc_test
GROUP BY id
) T2
ON T1.id = T2.id AND T1.runs = T2.MaxRuns
ORDER BY T2.id
;
Regards,
Brian
That should return the correct data but you are hitting the table twice instead of once. Performance wise this is going to be slower.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/