Search code examples
sqloracle-databasegreatest-n-per-grouporacle12c

Select X rows of each criteria

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.

Solution

  • 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;