Question

Produce a monotonically increasing numbered list of members, ordered by their date of joining. Remember that member IDs are not guaranteed to be sequential.
Schema reminder
DB schema

Expected Results

row_number firstname surname
1 GUEST GUEST
2 Darren Smith
3 Tracy Smith
4 Tim Rownam
5 Janice Joplette
6 Gerald Butters
7 Burton Tracy
8 Nancy Dare
9 Tim Boothe
10 Ponder Stibbons
11 Charles Owen
12 David Jones
13 Anne Baker
14 Jemima Farrell
15 Jack Smith
16 Florence Bader
17 Timothy Baker
18 David Pinker
19 Matthew Genting
20 Anna Mackenzie
21 Joan Coplin
22 Ramnaresh Sarwin
23 Douglas Jones
24 Henrietta Rumney
25 David Farrell
26 Henry Worthington-Smyth
27 Millicent Purview
28 Hyacinth Tupperware
29 John Hunt
30 Erica Crumpet
31 Darren Smith

Your Answer Hint Help Save Run Query

 

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.

Read up on the ROW_NUMBER window function.

Keyboard shortcuts:


Other hints: