CODEWITHSUNDEEP
sql
angel
angel

Reputation: 4632

select last 5 sale by customer?

I have a simple table called sales with columns

createdon, saleid, customerid

other table I have customer table with columns

customerid, name, etc..

now I need last 5 sales by client,

in other table called scanned I have 

scannedid,saleid,customerid

I believe I need to have the last 5 salesid by client for I get what clients were scanned in their last 5 sales ( sales are not same days for example a customer is visited monday a saturday, but other is visited tuesday and thursday but this information is not important for this example)

My real question is

How to get the customersid which were scanned in their last 5 sales?

For that I believe I do need the query for get last 5 salesid by client for example.i have 3 customers, then i'll need the last 5 sales, of customer1, customer2, and customer3, then i'll have 15 saleid returned

Upvotes: 0

Views: 1251

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

By client, I assume you mean customerid. I think you want the first five records from sales, based on the createdon date in descending order. The exact syntax depends on the database. Here is one way:

select top 5 customerid
from sales s
order by createdon desc

And another way:

select customerid
from sales s
order by createdon desc
limit 5

And another way:

select *
from (select customerid, rownum as seqnum
      from sales
      order by createdon
     ) s
where seqnum <= 5

Now that you've clarified what you want, it is easy:

select *
from (select s.*,
             row_number() over (partition by customerid order by created on desc) as seqnum
      from sales s
     ) s
where seqnum <= 5

Upvotes: 1

Related Questions