List each member's first booking after September 1st 2012
Question
Produce a list of each member name, id, and their first booking after September 1st 2012. Order by member ID.
Expected Results
| surname | firstname | memid | starttime |
|---|---|---|---|
| GUEST | GUEST | 0 | Sat Sep 01 2012 08:00:00 GMT+0000 (Coordinated Universal Time) |
| Smith | Darren | 1 | Sat Sep 01 2012 09:00:00 GMT+0000 (Coordinated Universal Time) |
| Smith | Tracy | 2 | Sat Sep 01 2012 11:30:00 GMT+0000 (Coordinated Universal Time) |
| Rownam | Tim | 3 | Sat Sep 01 2012 16:00:00 GMT+0000 (Coordinated Universal Time) |
| Joplette | Janice | 4 | Sat Sep 01 2012 15:00:00 GMT+0000 (Coordinated Universal Time) |
| Butters | Gerald | 5 | Sun Sep 02 2012 12:30:00 GMT+0000 (Coordinated Universal Time) |
| Tracy | Burton | 6 | Sat Sep 01 2012 15:00:00 GMT+0000 (Coordinated Universal Time) |
| Dare | Nancy | 7 | Sat Sep 01 2012 12:30:00 GMT+0000 (Coordinated Universal Time) |
| Boothe | Tim | 8 | Sat Sep 01 2012 08:30:00 GMT+0000 (Coordinated Universal Time) |
| Stibbons | Ponder | 9 | Sat Sep 01 2012 11:00:00 GMT+0000 (Coordinated Universal Time) |
| Owen | Charles | 10 | Sat Sep 01 2012 11:00:00 GMT+0000 (Coordinated Universal Time) |
| Jones | David | 11 | Sat Sep 01 2012 09:30:00 GMT+0000 (Coordinated Universal Time) |
| Baker | Anne | 12 | Sat Sep 01 2012 14:30:00 GMT+0000 (Coordinated Universal Time) |
| Farrell | Jemima | 13 | Sat Sep 01 2012 09:30:00 GMT+0000 (Coordinated Universal Time) |
| Smith | Jack | 14 | Sat Sep 01 2012 11:00:00 GMT+0000 (Coordinated Universal Time) |
| Bader | Florence | 15 | Sat Sep 01 2012 10:30:00 GMT+0000 (Coordinated Universal Time) |
| Baker | Timothy | 16 | Sat Sep 01 2012 15:00:00 GMT+0000 (Coordinated Universal Time) |
| Pinker | David | 17 | Sat Sep 01 2012 08:30:00 GMT+0000 (Coordinated Universal Time) |
| Genting | Matthew | 20 | Sat Sep 01 2012 18:00:00 GMT+0000 (Coordinated Universal Time) |
| Mackenzie | Anna | 21 | Sat Sep 01 2012 08:30:00 GMT+0000 (Coordinated Universal Time) |
| Coplin | Joan | 22 | Sun Sep 02 2012 11:30:00 GMT+0000 (Coordinated Universal Time) |
| Sarwin | Ramnaresh | 24 | Tue Sep 04 2012 11:00:00 GMT+0000 (Coordinated Universal Time) |
| Jones | Douglas | 26 | Sat Sep 08 2012 13:00:00 GMT+0000 (Coordinated Universal Time) |
| Rumney | Henrietta | 27 | Sun Sep 16 2012 13:30:00 GMT+0000 (Coordinated Universal Time) |
| Farrell | David | 28 | Tue Sep 18 2012 09:00:00 GMT+0000 (Coordinated Universal Time) |
| Worthington-Smyth | Henry | 29 | Wed Sep 19 2012 09:30:00 GMT+0000 (Coordinated Universal Time) |
| Purview | Millicent | 30 | Wed Sep 19 2012 11:30:00 GMT+0000 (Coordinated Universal Time) |
| Tupperware | Hyacinth | 33 | Thu Sep 20 2012 08:00:00 GMT+0000 (Coordinated Universal Time) |
| Hunt | John | 35 | Sun Sep 23 2012 14:00:00 GMT+0000 (Coordinated Universal Time) |
| Crumpet | Erica | 36 | Thu Sep 27 2012 11:30:00 GMT+0000 (Coordinated Universal Time) |
Your Answer
Your Results
Loading database...
Answers and Discussion
select mems.surname, mems.firstname, mems.memid, min(bks.starttime) as starttime
from cd.bookings bks
inner join cd.members mems on
mems.memid = bks.memid
where starttime >= '2012-09-01'
group by mems.surname, mems.firstname, mems.memid
order by mems.memid;This answer demonstrates the use of aggregate functions on dates. MIN works exactly as you'd expect, pulling out the lowest possible date in the result set. To make this work, we need to ensure that the result set only contains dates from September onwards. We do this using the WHERE clause.
You might typically use a query like this to find a customer's next booking. You can use this by replacing the date '2012-09-01' with the function now()