Every second value from table – sql

December 2, 2021

Suppose, I have some table like

| id | user_id | value |
|:---|:-------:| -----:|
|   1|        1|      a|
|   2|        1|      b|
|   3|        1|      c|
|   4|        2|      d|
|   5|        2|      e|
|   6|        3|      f|
|   7|        4|      g|
|   8|        4|      h|

So I need print all second values for every user, for example

| id | user_id | value |
|:---|:-------:| -----:|
|   2|        1|      b|
|   5|        2|      e|
|   8|        4|      h|

So, how can I do it?

>Solution :

Use an inline view and an analytic to generate a row number for each user_ID ordered by ID and then simply select where row number = 2.

SELECT ID, user_ID, Value
FROM (SELECT ID, user_ID, Value, Row_number() over (partition by user_ID order by ID) RN
      FROM table) InLineTable
WHERE RN=2

or use a common table expression to do the same

essentially what the row number does is for each user_Id it counts by 1 for each ID. it restarts for each new user_ID. Since we order by ID, we always get the 2nd ID.

WITH CTE AS (SELECT ID, user_ID, Value, Row_number() over (partition by user_ID 
         order by ID) RN
         FROM table)
SELECT ID, user_ID, Value
FROM CTE
WHERE RN=2