Select N random rows in group

Question

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.

an idea is to select 4 random rows from each group, separately, and then make unions. — wxyz
– wxyz, Commented Jan 9, 2014 at 8:25

Mahmoud Gamal · Accepted Answer · 2014-01-09 08:27:58Z

3

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, .

answered Jan 9, 2014 at 8:27

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

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

Collectives™ on Stack Overflow

Select N random rows in group

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related