Limiting rows in select statement

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 2000 Forums

Transact-SQL (2000)

Limiting rows in select statement

Author

Topic

Liza
Starting Member

11 Posts

Posted - 2003-08-08 : 17:46:34

Hi,

I am trying to create a select statement that will return the top 2 rows for each of the id's based on the most current dates. I am able to return the top row easiest enough using the max aggregate but can't get the 2nd row.

ex. EmployeeID EmployeeName StatusDate
1 Joe 05/02/2003
1 Joe 04/01/2002
1 Joe 07/25/2001
3 Jim 05/20/2002
3 Jim 06/20/2001

So, the results that I would like to get are the first 2 rows for Joe and both of the rows for Jim.

Any help given would be greatly appreciated.

Thanks,
Liza

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-08 : 18:00:24

Click and you shall find.

Liza
Starting Member

11 Posts

Posted - 2003-08-08 : 18:01:51

Thank-you very much SamC, must be a chick problem! :D

SamC
White Water Yakist

3467 Posts

Posted - 2003-08-08 : 18:06:24

SELECT EmployeeID, EmployeeName, StatusDate

FROM (

SELECT EmployeeID, EmployeeName, StatusDate,
(SELECT COUNT(*) FROM MyTable WHERE EmployeeID=M.EmployeeID AND StatusDate > M.StatusDate) as Rank
FROM MyTable M
) A

WHERE Rank <= 2

I had to code this once for myself. Looks like something I'll need in the near future anyway.

Sam

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2003-08-09 : 02:35:36

U can Also try this.
Replace @table variable with the actual Table Name.

Declare @table table (EmployeeID int, EmployeeName varchar(50), StatusDate datetime)
Insert into @table select 1,'Joe','05/02/2003'
Insert into @table select 1,'Joe','04/01/2002'
Insert into @table select 1,'Joe','07/25/2001'
Insert into @table select 2,'Jim','05/20/2003'
Insert into @table select 2,'Jim ','06/20/2002'
Insert into @table select 2,'Joe','04/01/2002'
Select EmployeeID,EmployeeName,StatusDate
from @table a
where StatusDate in (
select Top 2 statusDate from @table b where
a.EmployeeID=b.EmployeeID order by StatusDate desc)

V.Ganesh
NetAssetManagement.Com
vganesh76@rediffmail.com

Enjoy working

Liza
Starting Member

11 Posts

Posted - 2003-08-11 : 15:44:16

Thanks for the post vganesh76 but I have already created and implemented the solution from SamC, which works great!

Have a fantastic day!
Liza

Liza
Starting Member

11 Posts

Posted - 2003-08-13 : 12:29:52

Hey All,

They have added a new twist to this query. In addition to the previous post, they would only like to see the records that have a different OrgCode -

ex. EmployeeID EmployeeName StatusDate OrgCode
1 Joe 05/02/2003 77
1 Joe 04/01/2002 78
1 Joe 07/25/2001 78
3 Jim 05/20/2002 55
3 Jim 06/20/2001 55

So, given the above example, the only results that they would like to see are the 1st and 2nd rows for Joe because he had a change in OrgCode. Sorry to keep asking for help with this but I find it quite complex.

Thanks and have a great day!
Liza :D

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-13 : 13:44:44

Well... and what 'they' want to see in the case of Jim
with only two records and the same OrgCode?

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-14 : 03:05:05

select EmployeeID, EmployeeName, StatusDate, OrgCode
from u where

((select count(distinct OrgCode) from u q where
q.EmployeeID=u.EmployeeID)>1
and
StatusDate in (select top 2 max(StatusDate) from u p where
p.EmployeeID=u.EmployeeID group by EmployeeID, OrgCode
order by max(StatusDate) desc))

or

((select count(distinct OrgCode) from u q where
q.EmployeeID=u.EmployeeID)=1
and
StatusDate in (select top 2 StatusDate from u p where
p.EmployeeID=u.EmployeeID order by StatusDate desc))

order by 1, 3 desc

Liza
Starting Member

11 Posts

Posted - 2003-08-14 : 16:18:50

Hi Stoad,

In response to your earlier post, they don't want to see any records for Jim. The only rows that are returned are the top 2 rows according to Status Date for that employee and only for the employees that have 2 different OrgCodes for those 2 rows returned. Hope that makes sense. I will try to apply your solution, hopefully it works! Thanks for the posts.

Liza :D

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-14 : 16:27:11

Oh... then we need no the 2nd part after 'or' (here 'u' is your table):

select EmployeeID, EmployeeName, StatusDate, OrgCode
from u where

(select count(distinct OrgCode) from u q where
q.EmployeeID=u.EmployeeID)>1
and
StatusDate in (select top 2 max(StatusDate) from u p where
p.EmployeeID=u.EmployeeID group by EmployeeID, OrgCode
order by max(StatusDate) desc)

order by 1, 3 desc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources