How to find the second row in a table?

David_2

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()?

Abhishek_Rana

We can use RANK() OVER Partition for this :

Suppose you have following data in your table:
=================================

dbadmin=> select * from Transaction order by 1,2;

 clientid |  buy_date  | amount 
 ----------+------------+--------         
1 | 2013-01-01 |    200         
1 | 2013-01-04 |    300         
1 | 2013-01-06 |    400         
2 | 2013-01-06 |    400         
2 | 2013-02-06 |    500         
2 | 2013-02-07 |    600         
3 | 2013-02-03 |    600         
3 | 2013-02-04 |    600         
3 | 2013-02-05 |   1200

You can get get all clients second purchases as below:
=======================================

dbadmin=> select * from ( 
                    SELECT clientid, buy_date,amount, rank() 
                    OVER(PARTITION BY clientid 
                    ORDER BY buy_date) As RANKING 
                    FROM Transaction 
                                        ) as Trans where Trans.RANKING=2 
                    order by 1;

clientid |  buy_date  | amount | RANKING
----------+------------+--------+---------
1 | 2013-01-04 |    300 |       2         
2 | 2013-02-06 |    500 |       2       
 3 | 2013-02-04 |    600 |      2

(3 rows)

Regards'

Abhishek

Navin_C

Adding to the solution

if you want to know the second purchase of a client for a every day basis.

  select * from ( 
                    SELECT clientid, buy_date,amount, rank() 
                    OVER(PARTITION BY clientid,buy_date 
                    ORDER BY buy_date) As RANKING 
                    FROM Transaction 
                                        ) as Trans where Trans.RANKING=2 
                    order by 1;

Hope this helps