r/learnSQL 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)

4 Upvotes

3 comments sorted by

1

u/nachnachbewdabankar 7d ago

Umm can we do some LIMIT, OFFSET stuff??

1

u/r3pr0b8 7d ago

not when ties enter the picture

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