SQL Server 2000: How to get the top 10 for each individual field. Maybe a loop?

Question

I have the following table

MyTable
  ID
  MessageType
  MessageDate
  MessageBody

A table represents several million rows, but it has only 100 unique MessageType.

I need a sample of each type of MessageType (should include at least MessageType and MessageBody), but I can not do it DISTINCT, because it receives only the MessageType column.

I think something like

SELECT TOP 5 *
 FROM MyTable
 WHERE MessageType IN (SELECT DISTINCT MessageType FROM MyTable)

I know that this does not work, since this is only I am the top 5, but I am not sure how to make the SQL loop through this.

Thanks for any help

Answer 1

Row_Number Version

;WITH cte AS
(
SELECT   ID,
  MessageType,
  MessageDate,
  MessageBody,
  ROW_NUMBER() OVER (PARTITION BY MessageType ORDER BY (SELECT 0)) AS RN
FROM MyTable
)
SELECT   ID,
  MessageType,
  MessageDate,
  MessageBody
FROM cte 
WHERE RN <=5

Version CROSS APPLY

WITH m1 AS
(
SELECT DISTINCT MessageType
FROM MyTable
)

SELECT m2.*
FROM  m1
CROSS APPLY
(
SELECT TOP 5 *
 FROM MyTable m2
 WHERE m2.MessageType = m1.MessageType
 ) m2

Answer 2

, , , 5 . Marc_s .

, :

SELECT  ID,
        MessageType,
        MessageDate
FROM (
    SELECT ID,
           MessageType,
           MessageDate,
           ROW_NUMBER() OVER (PARTITION BY MessageType, ORDER BY NEWID() ) AS RN
       -- I am using NewID() because it will produce a nice random sampling, 
       -- but Mark SELECT(0) will be faster.
    FROM   MyTable
    ) sampling
WHERE RN =1