Finding the earliest purchase place for each unique user based-CodePudding

I need to get the userID and the marketplace, where the user first bought something.

The db looks like this:

userID, marketplace, dateOfOrder
74526,  Amazon,      2/1/2021
74526,  eBay,        1/1/2021
74526,  Walmart,     12/10/2021 
74525,  Amazon,      14/5/2021
74525,  eBay,        12/4/2021
74525,  Walmart,     9/10/2021

I need to get the following:

userID, marketplace
74526,  eBay
74525,  eBay

I tried several ways but neither is returning the result without multiple issues or increased complexity, and I am trying to keep the query as little as possible for whoever will maintain it in the future. Is there an easy way to achieve what I am looking for?

CodePudding user response：

You can use the ROW_NUMBER function to get the "first bought" row of each user. Add row numbering with:

row_number() over(partition by userID order by dateOfOrder asc) as r

and then extract where r=1 (first row = first bought based on date ordered ascending):

select userID, marketplace
from (
  select *, row_number() over(partition by userID order by dateOfOrder asc) as r
  from your_table
)
where r=1

CodePudding user response：

Sql Server features a nifty top(1) with ties .. order by for that

select top(1) with ties t.* 
from your_table t
order by row_number() over(partition by userid order by date);