Every second value from table

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?

CodePudding user response：

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

CodePudding user response：

You can use rank in postgres such as the following:

    select id, user_id, value
    from (
        select id, user_id, value, 
        rank() over (partition by user_id order by id) as u_rank
        from t1
        )z
    where u_rank = 2

Output:

    id  user_id value
    2   1       b
    5   2       e
    8   4       h