Is there a way to write SQL to get all clients second purchases in a table? (Sorting rows by date)
Table Transaction
clientid int
buy_date date
amount int
I prefer to use the Analytic functions, perhaps using count() over()?
How to find the second rows in a table?
Moderator: NorbertKrupa
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: How to find the second rows in a table?
I would highly encourage you to try it out yourself in the future.
Using this sample data:
The following query will return each client's second purchase based on buy_date:
The query orders the rows based on the clientid and sorts on buy_date. It then returns where the row number is 2. You can also use RANK().
Result
See the RANK() or ROW_NUMBER() documentation for more info.
Using this sample data:
Code: Select all
clientid | buy_date | amount
----------+------------+--------
1 | 2014-02-02 | 1
1 | 2014-02-03 | 5
1 | 2014-02-04 | 10
1 | 2014-02-05 | 15
1 | 2014-02-06 | 20
2 | 2014-02-02 | 1
2 | 2014-02-03 | 2
2 | 2014-02-04 | 3
2 | 2014-02-05 | 4
2 | 2014-02-06 | 5
Code: Select all
SELECT a.clientid,
a.buy_date,
a.amount
FROM (SELECT clientid,
buy_date,
amount,
ROW_NUMBER()
OVER (
PARTITION BY clientid
ORDER BY buy_date) AS rn
FROM tt) a
WHERE a.rn = 2
ORDER BY a.clientid;
Result
Code: Select all
clientid | buy_date | amount
----------+------------+--------
1 | 2014-02-03 | 5
2 | 2014-02-03 | 2
Checkout vertica.tips for more Vertica resources.