Select X rows of each criteria

Question

I can get a count of records based on some criteria such as length of the data in specific columns. But it seems I can get first X records (say 20 records) and they could all be the same length.

How do I get 20 records of each length?

SELECT LABEL_ID, DEST, WEIGHT_OZ 
FROM MYTABLE
WHERE
LENGTH(LABEL_ID) IN (10,13,24)
AND ROWNUM <= 20;

This returns 20 records of labels of length 10 (since there are more than 20 records of that length). How do I get 20 of length 10, 20 of length 13, 20 of length 24, etc.?

Thanks.

Does this answer your question? [Get top results for each group (in Oracle)](https://stackoverflow.com/questions/134958/get-top-results-for-each-group-in-oracle) — MT0, Jan 16 '23 at 11:49

score 1 · Accepted Answer · answered Jan 16 '23 at 05:46

Assisted by a post here

WITH rws AS (
    SELECT o.LABEL_ID, o.DEST, o.WEIGHT_OZ,
    ROW_NUMBER () OVER (
        PARTITION BY LENGTH(LABEL_ID)
        ORDER BY SOME_DATE_COLUMN DESC
    ) rn
    FROM MYTABLE o
    WHERE LENGTH(LABEL_ID) IN (10,13,24)
)
SELECT LABEL_ID, DEST, WEIGHT_OZ
FROM rws 
WHERE rn <= 20
ORDER BY LENGTH(LABEL_ID), SOME_DATE_COLUMN DESC;

Select X rows of each criteria

1 Answers1