Sql-server – Select 100 records (or less if there are not 100 records) per group from multiple tables

sql server

The following query gets me the entire universe of a.transactionId with b.dataItemValue:

SELECT DISTINCT a.transactionId, b.dataItemValue 'Round Type', c.transactionRoundTypeName 'Round Type Name'

FROM TransactionId_tbl a

INNER JOIN TransactionDataInteger_tbl b
ON a.transactionId = b.objectId
AND b.dataItemId = 601257

INNER JOIN TransactionRoundType_tbl c
ON b.dataItemValue = c.transactionRoundTypeId

WHERE b.dataItemValue is NOT NULL
AND b.dataItemValue != 7
ORDER BY b.dataItemValue

I need to limit this query to get only 100 records (or the number of existing records if there are less than 100) of a.transactionId for every record of b.dataItemValue

Thanks!

Best Answer

This should do what you need:

WITH MyCTE as (
    SELECT DISTINCT 
        a.transactionId, 
        b.dataItemValue, 
        c.transactionRoundTypeName,
    FROM TransactionId_tbl a
    INNER JOIN TransactionDataInteger_tbl b
        ON a.transactionId = b.objectId
        AND b.dataItemId = 601257
    INNER JOIN TransactionRoundType_tbl c
        ON b.dataItemValue = c.transactionRoundTypeId
    WHERE b.dataItemValue is NOT NULL
        AND b.dataItemValue != 7
    ) Src
    ORDER BY transactionId,
        dataItemValue
    ),
MyCTE2 as (
    SELECT transactionId,
        dataItemValue,
        transactionRoundTypeName,
        ROW_NUMBER() OVER (GROUP BY transactionId ORDER BY dataItemValue) RowId
    FROM MyCTE
    )
SELECT transactionId,
    dataItemValue as 'Round Type',
    TransactionRoundTypeName as 'Round Type Name'
FROM MyCTE2
WHERE RowId <= 100
ORDER BY TransactionId,
    RowId  -- Proxy for dataItemValue

This gives you up to 100 rows per DISTINCT transactionid and dataItemValue combination, and orders the data by transaction and rowId (proxy for 'Round Type'). If you really WANT it ordered by 'Round Type', change the last ORDER BY to

ORDER BY dataItemValue
Related Question