CODEWITHSUNDEEP
sqlsql-server
mrd
mrd

Reputation: 2183

Select N random rows in group

Please observe following SQL query which gets 12 random rows. 

SELECT TOP 12 ExportSystem.sysID, ExportSystem.sysNo, ExportSystem.sysName, Document.docArchiveNo FROM ExportSystem
INNER JOIN Customer ON ExportSystem.sysNo = Customer.expSysNr
INNER JOIN Department ON Customer.cusID = Department.cusID
INNER JOIN Level ON Level.levParentID = Department.levRootID
INNER JOIN Document ON Document.levID = Level.levID
GROUP BY ExportSystem.sysID, ExportSystem.sysNo, ExportSystem.sysName, Document.docArchiveNo
ORDER BY newid()

Results 

SystemName  ArchiveNo
RR  31676981
AA  28741154
AA  30435868
AA  29665408
AA 28116900
BB  31114199
RR  29080695
AA  29903701
VV  31890130
BB  29898481
BB  30892927
AA  30545346

I have tried many things but have not figured it out how to get same number of rows for each group. Currently there are two RR, six AA, three BB, and one VV. How to get 4 rows in each SystemName group.

Upvotes: 0

Views: 1771

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

You can use a ranking function ROW_NUMBER with PARTITION BY Systemname to do so:

WITH CTE
AS
(
   SELECT 
     ExportSystem.sysID, 
     ExportSystem.sysNo, 
     ExportSystem.sysName, 
     Document.docArchiveNo,
     ROW_NUMBER() OVER(PARTITION BY ExportSystem.sysName 
                       ORDER BY newid()) AS RN 
  FROM ExportSystem
  INNER JOIN Customer   ON ExportSystem.sysNo = Customer.expSysNr
  INNER JOIN Department ON Customer.cusID     = Department.cusID
  INNER JOIN Level      ON Level.levParentID  = Department.levRootID
  INNER JOIN Document   ON Document.levID     = Level.levID
)
SELECT 
  sysID, 
  sysNo, 
  sysName, 
  docArchiveNo
FROM CTE
WHERE RN <= 4;

Note that: Selecting the same columns in the GROUP BY in your original query is useless, there is no need to use GROUP BY. Remove it and use DISTINCT instead, .

Upvotes: 3

Related Questions