SELECT TOP(N) Rows For Each GROUP

Question

Here is my query thus far:

SELECT [Id], [HotelName], [StarRating], [Description], [CheckinDate], [CheckoutDate], [Price], [ImageUrl] FROM ( SELECT TOP (6) [Id], [HotelName], [StarRating], [Description], [CheckinDate], [CheckoutDate], [Price], [ImageUrl], RANK() OVER(PARTITION BY [StarRating] ORDER BY [StarRating]) AS Num FROM [dbo].[Hotel] WHERE [CityId] = @CityId AND CheckinDate > GETDATE() AND [StarRating] IN (3, 4, 5) ) X WHERE Num <= 2

What I want is to get 2 rows for each star rating: 2 of rating 3, 2 of rating 4 and 2 of rating 5. How can I do this? I have come up with the above after doing some research online already, but I am obviously not fully understanding hwo to implement it, because it is not working... I am getting 6 rows of star rating 3

Please see meta.stackexchange.com/questions/2950/… to learn why I removed the "thanks" from your question. — John Saunders
– John Saunders, Commented Oct 19, 2011 at 4:21

VinayC · Accepted Answer · 2011-10-19 04:27:50Z

8

Use ROW_NUMBER function - for example,

WITH X AS ( SELECT [Id], [HotelName], [StarRating], [Description], [CheckinDate], [CheckoutDate], [Price], [ImageUrl], ROW_NUMBER() OVER(PARTITION BY [StarRating] ORDER BY [Id]) AS Num FROM [dbo].[Hotel] WHERE [CityId] = @CityId AND CheckinDate > GETDATE() AND [StarRating] IN (3, 4, 5) ) SELECT [Id], [HotelName], [StarRating], [Description], [CheckinDate], [CheckoutDate], [Price], [ImageUrl] FROM X WHERE Num <= 2

answered Oct 19, 2011 at 4:27

VinayC

49.6k7 gold badges64 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jamie F Over a year ago

I'm deleting my answer, this is much better. I think the key difference between your query and this one is the ORDER BY change. You are asking to order by StarRating and RANK(), so you are getting all of the results tied for first by StarRating (3)...

Ben Thul Over a year ago

Just a little exposition as to why this one works an the original poster's didn't; the rank function will return the same for "ties". That is, in the original query, I suspect that every row had a value of 1 for the Num column because the partition by and order by were the same. By changing the order by to a unique value, you eliminate duplicates from the result set. Of course, using row_number will always do this. So there you go.

Collectives™ on Stack Overflow

SELECT TOP(N) Rows For Each GROUP

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related