nth result in Teradata result

Question

I'm trying to make a Teradata SQL query that will return the n-th chronological visit date for each user. E.g.,

user  |  visit_date
---------------------
  a      1/1      
  b      1/10
  c      1/20
  a      1/3
  a      1/4
  b      1/5
  c      1/15
  b      1/9


> magic_query_for_Second_visit;
user  |  second
------------------
  a       1/3
  b       1/9
  c       1/20

I tried something like the below, but Teradata shrieked that "Ordered analytical functions are not allowed in WHERE clause." I've pulled my hair for a while but am not making much progress. Anyone seen this?

select user,
  row_number() over (partition by user order by visit_date desc) as rnum

from visitstable
  where rnum = 2

If I exclude the where then my result looks promising... I just can't extract what I need!

 user |  visit_date | rnum
---------------------------
  a        1/1          1
  a        1/3          2
  a        1/4          3
  b        1/5          1
  b        1/9          2
  b        1/10         3 
  c        1/15         1
  c        1/20         2

Thanks in advance for the help!

Answer 1

Oh, heaven forbid I should look in the documentation. FYI, it looks like qualify lets you specify ordered analytic constraints. However, qualify isn't ANSI, so I'm still handing out points for a portable solution...

I can do:

select user,
  row_number() over (partition by user order by visit_date desc) as rnum

from visitstable
qualify rnum=2

...and get...

user  |   visit_date  |  rnum
-----------------------------
  a        1/3            2
  b        1/9            2
  c        1/20           2

Bookmark it! :)

Answer 2

SELECT 
    user

FROM 
    visitstable

QUALIFY ROW_NUMBER() OVER (
    PARTITION BY 
        user
    ORDER BY 
        visit_date DESC) = 2

I see from your comment that you don't necessarily want to rely on the Teradata-specific QUALIFY. The following is a sort-of portable solution (as many RDBMSes are now supporting CTEs) based on another chap's answer:

WITH tmp (user, rnum) AS (
    SELECT 
        user, 
        ROW_NUMBER() OVER (
            PARTITION BY 
                user 
            ORDER BY 
                visit_date DESC) AS rnum 
    FROM 
        visitstable)
SELECT
    tmp.*
FROM 
    tmp
WHERE 
    tmp.rnum = 2