CODEWITHSUNDEEP
teradata
Andrei Vasilev
Andrei Vasilev

Reputation: 607

How to get the last two rows

The table is I need to get the last two event associates for each event

event_id    event_date  event_associate
1           2/14/2014   ben
1           2/15/2014   ben
1           2/16/2014   steve
1           2/17/2014   steve   // this associate is the last but one for event 1
1           2/18/2014   paul    // this associate is the last  for event 1
2           2/19/2014   paul
2           2/20/2014   paul    // this associate is the last but one for event 2 
2           2/21/2014   ben     // this associate is the last  for event 2
3           2/22/2014   paul
3           2/23/2014   paul
3           2/24/2014   ben
3           2/25/2014   steve   // this associate is the last but one for event 3
3           2/26/2014   ben     // this associate is the last for event 3

I need to find out who was the last but one event_associate for each event . The result should be

  event_id      event_associate      rn
    1             steve             2
    1             paul              1
    2             paul              2
    2             ben               1
    3             steve             2
    3             ben               1

I tried 

SELECT t.* , ROW_NUMBER() OVER (PARTITION BY event_associate ORDER BY event_date DESC) rn 
FROM mytable t
QUALIFY rn < 3

Upvotes: 1

Views: 1416

Answers (2)

dnoeth
dnoeth

Reputation: 60462

"for each event" -> PARTITION BY event_id

"last but one" -> ORDER BY event_date DESC

Upvotes: 1

Karim Massi
Karim Massi

Reputation: 77

you have to count the number of row in your data base and then use this query 

        sql=sql+" LIMIT "+NumberOfRowsToShowInTables+" OFFSET "+(countrow-         NumberOfRowsToShowInTables);

where countrow is he number of row in your database

NumberOfRowsToShowInTables equal 2 as you mentioned sql is your normal query without limitation

Upvotes: 1

Related Questions