Skip to content
Advertisement

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

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?

Advertisement

Answer

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
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement