Select top X for each multiple item in column

Question

Select top X for each multiple item in column

domleg

SSC Enthusiast

Points: 141
More actions
December 5, 2013 at 7:33 am

#280766

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:
92NULL2011-11-17 19:26:57.0002011-11-17 19:32:15.0001018151716
92NULL2011-11-16 18:57:49.0002011-11-16 19:01:23.0001018151716
92NULL2011-11-16 08:09:15.0002011-11-16 08:17:35.0001018151716
92NULL2011-11-15 17:21:14.0002011-11-15 17:30:00.0001018151716
92NULL2011-11-14 22:46:03.0002011-11-14 22:50:56.0001018151716
92NULL2011-11-14 01:17:59.0002011-11-14 01:23:18.0001018151716
92NULL2011-11-13 11:14:47.0002011-11-13 11:20:42.0001018151716
92NULL2011-11-12 21:37:20.0002011-11-12 21:51:02.0001018151716
10730273252013-08-10 19:21:35.0002013-08-10 19:27:29.0001018151716
10730273252013-08-10 06:38:22.0002013-08-10 06:44:21.0001018151716
10730273252013-08-09 10:32:19.0002013-08-09 10:38:25.0001018151716
10730273252013-08-08 22:50:41.0002013-08-08 22:57:50.0001018151716
10730273252013-08-07 10:43:20.0002013-08-07 10:52:55.0001018151716
10730273252013-08-06 09:22:50.0002013-08-06 09:30:16.0001018151716
10730273252013-08-05 11:33:43.0002013-08-05 11:43:21.0001018151716
10730273252013-08-04 05:53:26.0002013-08-04 05:59:18.0001118151716
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.

Viewing 3 posts - 1 through 2 (of 2 total)

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

Keith Tate SSCrazy Eights Points: 9888 More actions · Answer 1

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 iswith 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[/url]

CKX SSCrazy Points: 2519 More actions · Answer 2

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:
92NULL2011-11-17 19:26:57.0002011-11-17 19:32:15.0001018151716
92NULL2011-11-16 18:57:49.0002011-11-16 19:01:23.0001018151716
92NULL2011-11-16 08:09:15.0002011-11-16 08:17:35.0001018151716
92NULL2011-11-15 17:21:14.0002011-11-15 17:30:00.0001018151716
92NULL2011-11-14 22:46:03.0002011-11-14 22:50:56.0001018151716
92NULL2011-11-14 01:17:59.0002011-11-14 01:23:18.0001018151716
92NULL2011-11-13 11:14:47.0002011-11-13 11:20:42.0001018151716
92NULL2011-11-12 21:37:20.0002011-11-12 21:51:02.0001018151716
10730273252013-08-10 19:21:35.0002013-08-10 19:27:29.0001018151716
10730273252013-08-10 06:38:22.0002013-08-10 06:44:21.0001018151716
10730273252013-08-09 10:32:19.0002013-08-09 10:38:25.0001018151716
10730273252013-08-08 22:50:41.0002013-08-08 22:57:50.0001018151716
10730273252013-08-07 10:43:20.0002013-08-07 10:52:55.0001018151716
10730273252013-08-06 09:22:50.0002013-08-06 09:30:16.0001018151716
10730273252013-08-05 11:33:43.0002013-08-05 11:43:21.0001018151716
10730273252013-08-04 05:53:26.0002013-08-04 05:59:18.0001118151716
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.