Set a row limit for each value in a WHERE clause

jb428j
jb428j used Ask the Experts™
on
I have multiple values in a WHERE clause and I would like to know how to limit the number of rows for each value.

About 20 rows would be great.

Current Statement:

SELECT *
FROM Table1
WHERE Col1 IN ('Value1','Value2',...'Value14')
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
Try UNION:



SELECT TOP 20 *
FROM Table
WHERE Col1 = 'Value1'
UNION ALL
SELECT TOP 20 *
FROM Table
WHERE Col1 = 'Value2'
UNION ALL
SELECT TOP 20 *
FROM Table
WHERE Col1 = 'Value3'
UNION ALL
... you get the idea...
UNION ALL
SELECT TOP 20 *
FROM Table
WHERE Col1 = 'Value14'

Open in new window

Author

Commented:
Thank you for the comment. I forgot to add the fact that I didn't want to do multiple UNION ALL statements. I was looking more inline with using ROW_NUMBER, but I am not an advanced SQL Guru.

Author

Commented:
I am working with another DBA here and it is looking to be a big pain in the rear and might now be worth the work. However, if someone does figure it out that would be awesome.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
might NOT* be worth the work
Chief Technology Officer
Most Valuable Expert 2011
Commented:
Please see this for explanation, though since you are asking for ROW_NUMBER() solution you may understand the concept already -- reference should help still with syntax.
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_1555-Analytical-SQL-Where-do-you-rank.html

SELECT {column list}
FROM (
SELECT *
     , ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY SomeColumn) RN
FROM Table1
WHERE Col1 IN ('Value1','Value2',...'Value14')
) derived
WHERE RN <= 20

The key piece is to PARTITION BY the Col1 values, then you will need to determine how they are ranked.  If it doesn't matter, you can order by Col1 and let it happen naturally or use another column like an id, etc.  You would derive this with the other columns you want and then filter where the resulting RN <= 20.

Hope that helps!
Top Expert 2010

Commented:
The good news is that I finally broke down and learned how to use ROW_NUMBER (although I still like to kick it old school, to ensure backward compatibility to SQL 2000).

The bad news is that mwvisa1 posted essentially the same code I came up with about 3 minutes before I returned.

:)
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
:) For my buddy, Patrick, here is how you could simulate ROW_NUMBER(), but maintain compatibility with SQL 2000 (explained in Article above).

SELECT {column list}
FROM (
SELECT *
     , COALESCE((
          SELECT COUNT(1) 
          FROM Table1 b 
          WHERE b.Col1 = a.Col1 
          AND b.SomeColumn < a.SomeColumn
       ), 0)+1 RN
FROM Table1 a
WHERE Col1 IN ('Value1','Value2',...'Value14')
) derived
WHERE RN <= 20

Open in new window

Author

Commented:
You guys are awesome. I appreciate your time and help on this! Fortunately, for me, I work with only SQL 2008. :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial