How to find the second rows in a table?

Moderator: NorbertKrupa

Post Reply
davidjhp
Newbie
Newbie
Posts: 3
Joined: Wed Feb 05, 2014 6:59 pm

How to find the second rows in a table?

Post by davidjhp » Thu Feb 06, 2014 6:56 pm

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

NorbertKrupa
GURU
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?

Post by NorbertKrupa » Thu Feb 06, 2014 8:45 pm

I would highly encourage you to try it out yourself in the future.

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
The following query will return each client's second purchase based on buy_date:

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; 
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

Code: Select all

 clientid |  buy_date  | amount
----------+------------+--------
        1 | 2014-02-03 |      5
        2 | 2014-02-03 |      2
See the RANK() or ROW_NUMBER() documentation for more info.
Checkout vertica.tips for more Vertica resources.

Post Reply

Return to “New to Vertica SQL”