Select max records - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Select max records

Author

Topic

SoonerAK
Starting Member

3 Posts

Posted - 2011-09-15 : 23:13:16

I have a table with 440K rows and running sql 2000. I like to select emp id max time and project that goes with the time. This is some sample data:

Emp ID Time Project
1 9/15/2011 17:51 A
1 9/15/2011 18:41 B
1 9/15/2011 21:31 C
1 9/15/2011 21:38 A
1 9/15/2011 22:33 C
2 9/15/2011 12:31 B
2 9/15/2011 13:01 C
2 9/15/2011 15:31 T
2 9/15/2011 16:38 T
2 9/15/2011 22:31 T
3 9/15/2011 16:30 A
3 9/15/2011 15:30 B
3 9/15/2011 14:31 E
3 9/15/2011 12:11 D
3 9/15/2011 12:02 C

I want results to be like this:

Emp ID Time Project
1 9/15/2011 22:33 C
2 9/15/2011 22:31 T
3 9/15/2011 16:30 A

Thanks in advance!

I thought about using sql like this:

Select emp_id,time,project
from table1 a
where a.time = (select max(b.time) from table1 b where a.emp_id = b.emp_id)

I'm not sure if this is the most efficient way of doing it since it will be used on a web based report.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-16 : 00:15:22

Sooner,

The thing that is throwing me off is that you have the max time and project for empid 1 on a date prior to the max date.
Are you only looking at the time portion of the datetime field for the "max" time? Or are you looking at the time and date? 9/16/2011 17:51 is later than 9/15/2011 22:33.

SoonerAK
Starting Member

3 Posts

Posted - 2011-09-16 : 00:45:25

Sorry, Flameblaster. I had a typo, the date should have been 15th not 16th, but I would consider the datetime when searching for Max. Thanks for the reply.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-16 : 00:52:11

Ok that makes sense...I thought I was going crazy :)
This should work...as always, I'm sure there are 30 people with less "wordy" solutions than mine, but this should do it:

[CODE]
select a.empid, b.[time], b.project
from (
select empid, max(row) as Seq
from (
select empid, [time], project, ROW_NUMBER() over (partition by empid order by [time], project) as row
from table1) X
group by empid) A
join (select empid, [time], project, ROW_NUMBER() over (partition by empid order by [time], project) as row
from table1) B on b.empid=a.empid and b.row=a.Seq
[/CODE]
Works but too complicated, see next post

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-16 : 01:13:59

Ok, after being very irritated with myself for making this so difficult, I went back to the drawing board. This is much more efficient:

[CODE]
select empid, [time], project
from (
select empid, [time], project, ROW_NUMBER() over (partition by empid order by [time] desc, project desc) as row
from table1) A
where A.row=1
[/CODE]

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-16 : 01:38:34

quote:
Originally posted by flamblaster

Ok, after being very irritated with myself for making this so difficult, I went back to the drawing board. This is much more efficient:

[CODE]
select empid, [time], project
from (
select empid, [time], project, ROW_NUMBER() over (partition by empid order by [time] desc, project desc) as row
from table1) A
where A.row=1
[/CODE]

What i think is SQL 200o doesn't support row_number() function.

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-16 : 01:46:06

This will work in SQL Server 2000

SELECT B.EmpID,B.Stime,A.Project FROM #temp1 A
INNER JOIN
(
SELECT EmpID,MAX(time) as Stime FROM #temp1
GROUP BY EmpID
)B
ON A.Time=B.Stime

Hope this help.

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-16 : 01:59:25

Ugh, sorry, not really all that familiar with 2000.

SoonerAK
Starting Member

3 Posts

Posted - 2011-09-16 : 07:42:31

Thank you Flameblaster and Ashishashish. I will try the query when I get to work this morning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources