开发者

Group only the adjacent rows

hi i have a table l开发者_Go百科ike 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


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


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
                )


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


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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜