1

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!

2

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
  • Corrected query - This gets the DISTINCT values for DataItemValue. The previous code could have had two or more rows per DataItemValue if there were two or more entries for the same dataItemValue. – Laughing Vergil Jun 12 at 18:32
  • Many thanks! This indeed does the trick. – Diego Jun 12 at 19:24
  • @Diego If the answer solved your issue could you accept the answer? As reference for future readers. Thanks :) – Randi Vertongen Jun 13 at 6:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.