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
Recent Comments