How to get top records for each category in SQL

Say we had this table:

which we created with this code for reference:

create or replace TABLE students (
    story_name varchar(255),
    user_name varchar(255),
    age int,
    reading_counts int
);

INSERT INTO students (story_name, user_name, age, reading_counts)
VALUES
    (
        'Humpty Dumpty',
        'Andrea',
        5,
        10
    ),
    (
        'Wheels',
        'Simon',
        3,
        5
    ),
    (
        'Nemo',
        'Ali',
        2,
        20
    ),
    (
        'Blues Clues',
        'Andrea',
        5,
        2
    ),
    (
        'Story 100',
        'Simon',
        3,
        3
    ),
    (
        'Wheels',
        'Ali',
        2,
        5
    );

& we wanted to get the story name with the highest reading count for each user, we’d use a rank query like so:

SELECT *
 FROM (
   SELECT story_name, user_name, reading_counts, Rank() 
     over (PARTITION BY user_name
           ORDER BY reading_counts DESC ) AS rank
   FROM students
 ) WHERE rank <= 1

Our results would be:

Ali: “Nemo” – Reading Count: 20
Andrea: “Humpty Dumpty” – Reading Count: 10
Simon: “Wheels” – Reading Count: 5

Leave a Reply