Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Select top X for each multiple item in column Expand / Collapse
Author
Message
Posted Thursday, December 5, 2013 7:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 8, 2014 1:49 PM
Points: 7, Visits: 14
I have the following script:

SELECT dbo.HemAnimal.AniUserNumber, dbo.HemAnimal.AniResponderNumber, dbo.PrmDeviceVisit.DviStartTime, dbo.PrmDeviceVisit.DviEndTime,
dbo.PrmDeviceVisit.DviMilkVisit, dbo.PrmDeviceVisit.DviFailure, dbo.PrmMilkVisit.MviLFTeatWidth, dbo.PrmMilkVisit.MviLRTeatWidth,
dbo.PrmMilkVisit.MviRFTeatWidth, dbo.PrmMilkVisit.MviRRTeatWidth
FROM dbo.HemAnimal INNER JOIN
dbo.PrmDeviceVisit ON dbo.HemAnimal.AniId = dbo.PrmDeviceVisit.DviAniId INNER JOIN
dbo.PrmMilkVisit ON dbo.PrmDeviceVisit.DviId = dbo.PrmMilkVisit.MviDviId
ORDER BY dbo.HemAnimal.AniUserNumber, dbo.PrmDeviceVisit.DviEndTime DESC

Part of results:
92 NULL 2011-11-17 19:26:57.000 2011-11-17 19:32:15.000 1 0 18 15 17 16
92 NULL 2011-11-16 18:57:49.000 2011-11-16 19:01:23.000 1 0 18 15 17 16
92 NULL 2011-11-16 08:09:15.000 2011-11-16 08:17:35.000 1 0 18 15 17 16
92 NULL 2011-11-15 17:21:14.000 2011-11-15 17:30:00.000 1 0 18 15 17 16
92 NULL 2011-11-14 22:46:03.000 2011-11-14 22:50:56.000 1 0 18 15 17 16
92 NULL 2011-11-14 01:17:59.000 2011-11-14 01:23:18.000 1 0 18 15 17 16
92 NULL 2011-11-13 11:14:47.000 2011-11-13 11:20:42.000 1 0 18 15 17 16
92 NULL 2011-11-12 21:37:20.000 2011-11-12 21:51:02.000 1 0 18 15 17 16
107 3027325 2013-08-10 19:21:35.000 2013-08-10 19:27:29.000 1 0 18 15 17 16
107 3027325 2013-08-10 06:38:22.000 2013-08-10 06:44:21.000 1 0 18 15 17 16
107 3027325 2013-08-09 10:32:19.000 2013-08-09 10:38:25.000 1 0 18 15 17 16
107 3027325 2013-08-08 22:50:41.000 2013-08-08 22:57:50.000 1 0 18 15 17 16
107 3027325 2013-08-07 10:43:20.000 2013-08-07 10:52:55.000 1 0 18 15 17 16
107 3027325 2013-08-06 09:22:50.000 2013-08-06 09:30:16.000 1 0 18 15 17 16
107 3027325 2013-08-05 11:33:43.000 2013-08-05 11:43:21.000 1 0 18 15 17 16
107 3027325 2013-08-04 05:53:26.000 2013-08-04 05:59:18.000 1 1 18 15 17 16


However this gives me all the items in the database for AniUserNumber. I want to be able to get only the top 3 records for each AniUserNumber in the database table PrmMilkVisit, AniUserNumber is already a unique entry in the HemAnimal table but when Inner Joined with PrmDeviceVisit and PrmMilkVisit there are many visits for each animal, I only want the top 3 visits.
Post #1520073
Posted Thursday, December 5, 2013 7:47 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 2:23 PM
Points: 593, Visits: 928
Welcome! Please take a look at the article in my signature about posting questions to the forums. Table definitions and sample data is always useful. What I would recommend is
with cte as
(SELECT ROW_NUMBER() over (partition by dbo.HemAnimal.AniUserNumber order by dbo.PrmDeviceVisit.DviEndTime DESC) as rowNum, dbo.HemAnimal.AniUserNumber, dbo.HemAnimal.AniResponderNumber, dbo.PrmDeviceVisit.DviStartTime, dbo.PrmDeviceVisit.DviEndTime,
dbo.PrmDeviceVisit.DviMilkVisit, dbo.PrmDeviceVisit.DviFailure, dbo.PrmMilkVisit.MviLFTeatWidth, dbo.PrmMilkVisit.MviLRTeatWidth,
dbo.PrmMilkVisit.MviRFTeatWidth, dbo.PrmMilkVisit.MviRRTeatWidth
FROM dbo.HemAnimal INNER JOIN
dbo.PrmDeviceVisit ON dbo.HemAnimal.AniId = dbo.PrmDeviceVisit.DviAniId INNER JOIN
dbo.PrmMilkVisit ON dbo.PrmDeviceVisit.DviId = dbo.PrmMilkVisit.MviDviId
)
select *
from CTE
where rowNum <= 3

This should give you what you want.




Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1520088
Posted Thursday, December 5, 2013 7:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 6, 2014 8:07 AM
Points: 70, Visits: 511
domleg (12/5/2013)
I have the following script:

SELECT dbo.HemAnimal.AniUserNumber, dbo.HemAnimal.AniResponderNumber, dbo.PrmDeviceVisit.DviStartTime, dbo.PrmDeviceVisit.DviEndTime,
dbo.PrmDeviceVisit.DviMilkVisit, dbo.PrmDeviceVisit.DviFailure, dbo.PrmMilkVisit.MviLFTeatWidth, dbo.PrmMilkVisit.MviLRTeatWidth,
dbo.PrmMilkVisit.MviRFTeatWidth, dbo.PrmMilkVisit.MviRRTeatWidth
FROM dbo.HemAnimal INNER JOIN
dbo.PrmDeviceVisit ON dbo.HemAnimal.AniId = dbo.PrmDeviceVisit.DviAniId INNER JOIN
dbo.PrmMilkVisit ON dbo.PrmDeviceVisit.DviId = dbo.PrmMilkVisit.MviDviId
ORDER BY dbo.HemAnimal.AniUserNumber, dbo.PrmDeviceVisit.DviEndTime DESC

Part of results:
92 NULL 2011-11-17 19:26:57.000 2011-11-17 19:32:15.000 1 0 18 15 17 16
92 NULL 2011-11-16 18:57:49.000 2011-11-16 19:01:23.000 1 0 18 15 17 16
92 NULL 2011-11-16 08:09:15.000 2011-11-16 08:17:35.000 1 0 18 15 17 16
92 NULL 2011-11-15 17:21:14.000 2011-11-15 17:30:00.000 1 0 18 15 17 16
92 NULL 2011-11-14 22:46:03.000 2011-11-14 22:50:56.000 1 0 18 15 17 16
92 NULL 2011-11-14 01:17:59.000 2011-11-14 01:23:18.000 1 0 18 15 17 16
92 NULL 2011-11-13 11:14:47.000 2011-11-13 11:20:42.000 1 0 18 15 17 16
92 NULL 2011-11-12 21:37:20.000 2011-11-12 21:51:02.000 1 0 18 15 17 16
107 3027325 2013-08-10 19:21:35.000 2013-08-10 19:27:29.000 1 0 18 15 17 16
107 3027325 2013-08-10 06:38:22.000 2013-08-10 06:44:21.000 1 0 18 15 17 16
107 3027325 2013-08-09 10:32:19.000 2013-08-09 10:38:25.000 1 0 18 15 17 16
107 3027325 2013-08-08 22:50:41.000 2013-08-08 22:57:50.000 1 0 18 15 17 16
107 3027325 2013-08-07 10:43:20.000 2013-08-07 10:52:55.000 1 0 18 15 17 16
107 3027325 2013-08-06 09:22:50.000 2013-08-06 09:30:16.000 1 0 18 15 17 16
107 3027325 2013-08-05 11:33:43.000 2013-08-05 11:43:21.000 1 0 18 15 17 16
107 3027325 2013-08-04 05:53:26.000 2013-08-04 05:59:18.000 1 1 18 15 17 16


However this gives me all the items in the database for AniUserNumber. I want to be able to get only the top 3 records for each AniUserNumber in the database table PrmMilkVisit, AniUserNumber is already a unique entry in the HemAnimal table but when Inner Joined with PrmDeviceVisit and PrmMilkVisit there are many visits for each animal, I only want the top 3 visits.



Posting DDL and expected results almost always means someone gives you a coded response to your issue.

I think what you need here is the use of ROW_NUMBER partitioning on the relevant column and then just grabbing the rows you need based on the Row_Number Column value.
No DDL or expected results posted so can't show you to see if its whats you require.
Post #1520091
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse