Ranking rows

A

Analytic functions ranking rows.

WITH d AS (
    SELECT 'A' AS group_name, 11 AS id FROM DUAL UNION ALL
    SELECT 'A' AS group_name, 11 AS id FROM DUAL UNION ALL          -- dupe
    SELECT 'B' AS group_name, 21 AS id FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 22 AS id FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 22 AS id FROM DUAL UNION ALL          -- dupe
    SELECT 'C' AS group_name, 31 AS id FROM DUAL UNION ALL
    SELECT 'D' AS group_name, 41 AS id FROM DUAL UNION ALL
    SELECT 'D' AS group_name, 42 AS id FROM DUAL UNION ALL
    SELECT 'D' AS group_name, 42 AS id FROM DUAL                    -- dupe
)
SELECT
    d.*,
    ROW_NUMBER()    OVER (ORDER BY d.group_name, d.id)              AS row_id,
    ROW_NUMBER()    OVER (PARTITION BY d.group_name ORDER BY d.id)  AS group_row_id,
    --
    RANK()          OVER (ORDER BY d.group_name, d.id)              AS rank,            -- rank duplicates
    DENSE_RANK()    OVER (ORDER BY d.group_name, d.id)              AS dense_rank,      -- skip duplicates
    PERCENT_RANK()  OVER (ORDER BY d.group_name, d.id)              AS perc_rank
FROM d
ORDER BY 1, 2;

Get first/last row without inner select:

WITH d AS (
    SELECT 'A' AS group_name, 11 AS id FROM DUAL UNION ALL
    SELECT 'A' AS group_name, 12 AS id FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 21 AS id FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 22 AS id FROM DUAL UNION ALL
    SELECT 'B' AS group_name, 23 AS id FROM DUAL UNION ALL
    SELECT 'C' AS group_name, 31 AS id FROM DUAL UNION ALL
    SELECT 'C' AS group_name, 32 AS id FROM DUAL UNION ALL
    SELECT 'C' AS group_name, 33 AS id FROM DUAL UNION ALL
    SELECT 'D' AS group_name, 41 AS id FROM DUAL
)
SELECT
    d.group_name,
    MIN(d.id) KEEP (DENSE_RANK FIRST ORDER BY d.id)         AS first_id,
    MAX(d.id) KEEP (DENSE_RANK FIRST ORDER BY d.id DESC)    AS last_id
FROM d
GROUP BY d.group_name
ORDER BY 1;