Set a row limit for each value in a WHERE clause

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'

Select allOpen in new window

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.

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.

might NOT* be worth the work

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.

:)

:) 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

Select allOpen in new window

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