Need Top 3 or Top n number of rows from a group of rows

Question

Post reply

Need Top 3 or Top n number of rows from a group of rows

tknoobOLD

Ten Centuries

Points: 1183
More actions
October 2, 2006 at 5:26 pm

#153212

Hi All,

I'm trying to get the Top 3 or Top n number of rows (latest entered by date) of grouped data. From the following query:

SELECT Last_Name, First_Name, Task_Number, Detail_Work_Performed, Detail_Action_Date

FROM ProjectData

GROUP BY Last_Name, First_Name, Task_Number, Detail_Action_Date, Detail_Work_Performed

ORDER BY Last_Name, First_Name, Task_Number, Detail_Action_Date desc

How would I get the Top 3 latest entered rows (from Detail_Action_Date) grouped by the Task_Number?

Sometimes I may have 10+ rows for a particulary Task_Number but only want to display the latest 3.

Thanks for your help.

Tim

Viewing 6 posts - 1 through 5 (of 5 total)

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

SQLBill SSC Guru Points: 51440 More actions · Answer 1

SQLBill

SSC Guru

Points: 51440

October 2, 2006 at 5:46 pm

#663740

Does it have to be ordered in this way:

ORDER BY Last_Name, First_Name, Task_Number, Detail_Action_Date desc

If you can ORDER BY Detail_Action_Date,

Then just add TOP 3 to the script.

-SQLBill

SQLBill SSC Guru Points: 51440 More actions · Answer 2

SQLBill

SSC Guru

Points: 51440

October 2, 2006 at 5:48 pm

#663742

BTW- please don't cross post.

-SQLBill

tknoobOLD Ten Centuries Points: 1183 More actions · Answer 3

Actually, what I'm looking for is the top 3 for each group of Task Numbers. So for the following data:

Last_Name First_Name Task_Number Detail_Action_Date

Jones Rod 2000 09/29/06

Jones Rod 2000 09/27/06

Jones Rod 2000 09/23/06

Jones Rod 2000 09/11/06

Jones Rod 3000 10/01/06

Jones Rod 3000 09/26/06

Jones Rod 3000 09/20/06

Jones Rod 3000 09/19/06

Smith John 1000 10/01/06

Smith John 1000 09/23/06

Smith John 1000 09/21/06

Smith John 1000 09/19/06

Smith John 1000 09/05/06

I'm looking for output such as:

Last_Name First_Name Task_Number Detail_Action_Date

Jones Rod 2000 09/29/06

Jones Rod 2000 09/27/06

Jones Rod 2000 09/23/06

Jones Rod 3000 10/01/06

Jones Rod 3000 09/26/06

Jones Rod 3000 09/20/06

Smith John 1000 10/01/06

Smith John 1000 09/23/06

Smith John 1000 09/21/06

Thanks.

Farrell Keough SSCoach Points: 17414 More actions · Answer 4

Try this:

DECLARE @Task TABLE( PK integer IDENTITY(1,1),

Last_Name varchar(10),

First_Name varchar(10),

Task_Number integer,

Detail_Action_Date smalldatetime)

INSERT INTO @Task

SELECT 'Jones', 'Rod', 2000, '09/29/06' UNION ALL

SELECT 'Jones', 'Rod', 2000, '09/27/06' UNION ALL

SELECT 'Jones', 'Rod', 2000, '09/23/06' UNION ALL

SELECT 'Jones', 'Rod', 2000, '09/11/06' UNION ALL

SELECT 'Jones', 'Rod', 3000, '10/01/06' UNION ALL

SELECT 'Jones', 'Rod', 3000, '09/26/06' UNION ALL

SELECT 'Jones', 'Rod', 3000, '09/20/06' UNION ALL

SELECT 'Jones', 'Rod', 3000, '09/19/06' UNION ALL

SELECT 'Smith', 'John', 1000, '10/01/06' UNION ALL

SELECT 'Smith', 'John', 1000, '09/23/06' UNION ALL

SELECT 'Smith', 'John', 1000, '09/21/06' UNION ALL

SELECT 'Smith', 'John', 1000, '09/19/06' UNION ALL

SELECT 'Smith', 'John', 1000, '09/05/06'

SELECT t1.Last_Name, t1.First_Name, t1.Task_Number, CONVERT( varchar, t1.Detail_Action_Date, 101) AS Detail_Action_Date

FROM @Task t1

WHERE t1.PK IN( SELECT TOP 3 t2.PK

FROM @Task t2

WHERE t1.Last_Name + t1.First_Name + CONVERT( varchar, t1.Task_Number) = t2.Last_Name + t2.First_Name + CONVERT( varchar, t2.Task_Number)

ORDER BY t2.Task_Number, t2.Detail_Action_Date DESC)

I wasn't born stupid - I had to study.

tknoobOLD Ten Centuries Points: 1183 More actions · Answer 5

tknoobOLD

Ten Centuries

Points: 1183

October 3, 2006 at 10:44 am

#663903

Wow. Works great. Thanks.