CODEWITHSUNDEEP
sqlmysql
Luca Romagnoli
Luca Romagnoli

Reputation: 12455

Group only the adjacent rows

hi i have a table like this:

notifies

id,user_id
1,3
2,3
3,4
4,5
5,6
6,3
7,4
8,4
9,3
10,3

I have to create a query that group only the adjacent rows

So, the result of this example should be:

user_id
3
4
5
6
3
4
3

How can i do that? thanks

Upvotes: 4

Views: 3008

Answers (5)

Paul Powell
Paul Powell

Reputation: 1

Late to the party, but I came up with:

WITH notifiesgrouped AS (
SELECT id, user_id, 
grp=ROW_NUMBER() OVER (ORDER BY id) - ROW_NUMBER() OVER (PARTITION BY user_ID ORDER BY ID)
FROM notifies)
SELECT USER_ID FROM notifiesgrouped GROUP BY USER_ID, grp ORDER BY MIN(id)

So, use a CTE to generate the row_number and take away the row number partitioned by id - which gives:

id  user_id grp
1   3   0
2   3   0
3   4   2
4   5   3
5   6   4
6   3   3
7   4   5
8   4   5
9   3   5
10  3   5

and then group by userid and grp - order by min(id) to maintain the ordering. Giving:

USER_ID
3
4
5
6
3
4
3

Upvotes: 0

user3899864
user3899864

Reputation:

Try this:

  1. Select the notifies table, and place a row number using the ROW_NUMBER() command, ordered by id (call it id_no)
  2. Create a sub-select query also using row number (call it prev_id_no), but only selects the previous row (where prev_id_no = (id_no - 1))
  3. Nest the two select statements above under a SELECT * FROM (), so that you can manipulate the row_numbers
  4. Create a column which evaluates if #1 user_id is equal to #2 user_id. Use "CASE THEN 'YES ELSE 'NO' AS [DUPLICATE]"
  5. Nest the final resulting table from #3 and filter by DUPLICATE = 'NO'

SQL is this:

SELECT [user_id] FROM (
    SELECT *,
    CASE WHEN (
        SELECT [user_id] FROM (
            SELECT ROW_NUMBER() OVER(ORDER BY id) AS [prev_id_no], 
            [user_id]

            FROM notifies
        ) FILTER_IN

        WHERE FILTER_IN.prev_id_no = (FILTER_OUT.id_no - 1)
    ) = FILTER_OUT.[user_id] THEN 'YES' ELSE 'NO' END AS [DUPLICATE]

    FROM (
        SELECT ROW_NUMBER() OVER(ORDER BY id) AS [id_no], 
        [user_id]

        FROM notifies
    ) FILTER_OUT
) FILTER_FINAL

WHERE FILTER_FINAL.DUPLICATE = 'NO'

Result is:

user_id
3
4
5
6
3
4
3

Upvotes: 0

Michas
Michas

Reputation: 9428

I think the best option is to make quite plain select, put result to some application and filter it there in more suitable (imperative) language. However, if You need, my pure MySQL versions. You have to order data by id.

SELECT a.user_id
FROM notifies AS a
LEFT JOIN notifies AS c ON (
    SELECT MIN(id) FROM notifies AS b WHERE b.id > a.id
) = c.id
WHERE a.user_id <> c.user_id OR c.user_id IS NULL
ORDER BY a.id

Second example:

SELECT c.user_id
    FROM (
    SELECT a.id, a.user_id, MIN(b.id) AS next
    FROM notifies AS a
    LEFT JOIN notifies AS b ON b.id > a.id
    GROUP BY a.id, a.user_id

) AS c
LEFT JOIN notifies AS d ON d.id = c.next
WHERE c.user_id <> d.user_id OR c.next IS NULL
ORDER BY c.id

Upvotes: 0

Thomas
Thomas

Reputation: 64635

Select N.id, N.user_id
From notifies As N
Where Exists    (
                Select 1
                From notifies As N2
                Where N2.id = N.id + 1
                    And N2.user_id <> N.user_id
                )

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425251

SELECT  user_id
FROM    notifies n
WHERE   NOT
        (
        SELECT  user_id
        FROM    notifies ni
        WHERE   ni.id < n.id
        ORDER BY
                id DESC
        LIMIT 1
        ) <=> user_id

Upvotes: 5

Related Questions