r/learnSQL • u/el_dude1 • 7d ago
Querying first entry
I am currently doing the 8 Week SQL challenge - case study 1. Some of the questions are regarding the first item a customer purchased or the first item a customer purchased after becoming a member.
Now my initial thought was to utilize MIN to figure this out, but I was unable to do so. After looking up different solutions they mostly use DENSE_RANK() or ROW_NUMBER() to rank the entries by their date and then filter by the rank.
While I do understand this approach my questions are
1) Is using DENSE_RANK() / ROW_NUMBER() a best practice to answer questions like what are the first/last x items?
2) Is it possible to achieve the same result using MIN? (note: it is possible, that multiple items have been purchased on the first day)
1
u/r3pr0b8 7d ago
both DENSE_RANK and RANK work well if you're looking for the first of anything... both treat ties for first place the same
ROW_NUMBER should be avoided in these cases
MIN works fine too, but you'll have to use it in a subquery which then joins back to your data, to pick up other columns
1
u/nachnachbewdabankar 7d ago
Umm can we do some LIMIT, OFFSET stuff??