Produce a numbered list of members
Question
Produce a monotonically increasing numbered list of members (including guests), ordered by their date of joining. Remember that member IDs are not guaranteed to be sequential.Schema reminder ▼
Your Answer
Answers and Discussion Show
select row_number() over(order by joindate), firstname, surname from cd.members order by joindate
This exercise is a simple bit of window function practise! You could just as easily use count(*) over(order by joindate) here, so don't worry if you used that instead.
In this query, we don't define a partition, meaning that the partition is the entire dataset. Since we define an order for the window function, for any given row the window is: start of the dataset -> current row.