Group only adjacent lines

Question

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 need to create a query that groups only adjacent rows

So, the result of this example should be:

user_id
3
4
5
6
3
4
3

How can i do this? thank

Answer 1

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

Answer 2

I think the best option is to make a fairly simple choice, put the result in some application and filter it there in a more appropriate (imperative) language. However, if you need my pure MySQL versions. You must order data 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

Answer 3

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
                )

Answer 4

:

• , ROW_NUMBER(), id ( id_no)
• , ( prev_id_no), ( prev_id_no = (id_no - 1))
• Set up the two select statements above under SELECT * FROM () so you can manipulate row_numbers
• Create a column that evaluates whether # 1 user_id is equal to # 2 user_id. Use "CASE THEN" YES ELSE "NO" AS [DUPLICATE] "
• The socket of the final summary table from No. 3 and the filter DUPLICATE = 'NO'

SQL:

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:

user_id
3
4
5
6
3
4
3