SQL query to find the second occurrence of an element in a database-CodePudding

For example I have this table:

seenID | personID | seenTime
------- ---------- ---------
   108      3         13:34
   109      2         13:56
   110      3         14:22
   111      3         14:31
   112      4         15:04
   113      2         15:52

I want to get the row where the PersonID occurs for the second time.

So the desired output needs to be (for one of the person ID) :

seenID | personID | seenTime
------- ---------- ---------
   110      3         14:22

What would be the query for this scenario?

CodePudding user response：

For one particular person

Use OFFSET and LIMIT.

select *
from mytable
where personid = 3
order by seentime
offest 1
limit 1;

(From what I read, there once was a single DBMS called Presto, which was also called PrestoDB and PrestoSQL at times. This split into two different products, Presto and Trino. If you are using Trino, you can replace the proprietary LIMIT clause with a standard SQL FETCH clause.)

Per person

Number the rows with standard SQL's analytic function ROW_NUMBER and keep all rows that got numbered second.

select seenid, personid, seentime
from
(
  select
    seenid, personid, seentime,
    row_number() over (partition by personid order by seentime) as rn
  from mytable
) numbered
where rn = 2
order by personid;

CodePudding user response：

Use ROW_NUMBER() Function For this scenario

Please Refer this link for how to use this function different way ROW_NUMBER()

Query In > SQL Server (T-SQL)

   SELECT seenID,personID,seenTime FROM 
    (
        SELECT ROW_NUMBER() OVER(PARTITION BY personID ORDER BY personID) AS row_num,* 
        FROM "YourTableName"
    )AS T
WHERE row_num = 2