Mysql: Group rows with same value until the value is changed

Question

My table look like this:

   |ID| |Userid|    |ItemId|
    10        1            10
     9        1             9
     8        1             8
     7       38             7
     6       38             6
     5       38             5
     4        1             4
     3        1             3
     2        1             2
     1        1             1

My sql request is:

SELECT *
FROM nfeeds n1
WHERE EXISTS
(SELECT friendId
    FROM friends f WHERE friendId = n1.userid AND f.userid = 'my_userid')
GROUP BY userid
ORDER BY id DESC

And result is:

|ID|    |Userid|    |ItemId|
10        1            10
 7       38             7

I would like the result like this:

|ID|    |Userid|    |ItemId|
10        1            10
 7       38             7
 4        1             4

Thanks.

Does id always equal item_id? How convenient! – Strawberry Oct 26 '14 at 09:43 — Strawberry, Oct 26 '14 at 09:43
it is unclear what you are asking, please explain – Oct 26 '14 at 09:44 — , Oct 26 '14 at 09:44
...and is id always contiguous? – Strawberry Oct 26 '14 at 10:00 — Strawberry, Oct 26 '14 at 10:00
yes, i want to select the last id of same value – vaneayoung Oct 26 '14 at 10:03 — vaneayoung, Oct 26 '14 at 10:03

score 2 · Accepted Answer · answered Oct 26 '14 at 10:24

Note: This assumes contiguous ids (no gaps)...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table 
(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,Userid INT NOT NULL
,ItemId INT NOT NULL
);

INSERT INTO my_table VALUES
(10 ,1,4),
(9  ,1,9),
(8  ,1,3),
(7 ,38,7),
(6 ,38,2),
(5 ,38,1),
(4  ,1,5),
(3  ,1,8),
(2  ,1,6),
(1  ,1,9);

SELECT x.* 
  FROM my_table x
  JOIN
     (
       SELECT MIN(c.id) id 
         FROM my_table a
         LEFT 
         JOIN my_table b 
           ON b.userid = a.Userid 
          AND b.id = a.id - 1 
         LEFT 
         JOIN my_table c 
           ON c.Userid = a.userid
          AND c.id >= a.id
         LEFT
         JOIN my_table d
           ON d.Userid = a.userid
          AND d.id = c.id + 1
        WHERE b.id IS NULL 
          AND c.id IS NOT NULL
          AND d.id IS NULL
        GROUP 
           BY a.id
     ) y
    ON y.id = x.id; 
+----+--------+--------+
| ID | Userid | ItemId |
+----+--------+--------+
|  4 |      1 |      5 |
|  7 |     38 |      7 |
| 10 |      1 |      4 |
+----+--------+--------+

http://sqlfiddle.com/#!2/ffc8e1/1

score 2 · Answer 2 · answered Oct 26 '14 at 12:32

MySQL offers a pretty efficient way to do this, using variables. The more efficient solution doesn't use a temporary table or any joins. Here is a method assuming that you want the maximum user id and item id for each group (it is unclear whether you want the max or the first value):

select max(id) as userid, max(item_id) as item_id
from (select n.*,
             (@grp := if(@u = userid, @grp,
                         if(@u := userid, @grp + 1, @grp + 1)
                        )
             ) grp
      from nfeeds n cross join
           (select @grp := 0, @u := -1) vars
      order by id desc
     ) n
group by grp, userid;

score 1 · Answer 3 · edited May 23 '17 at 12:33

If I understand correctly what you're trying to do you have table nfeeds which contains sequences of records done by different users (in your example sequence 10,9,8 by user 1, 7,6,5 by user 38 and sequence 4,3,2,1 again by user 1). And you want to select the latest record for each sequence.

I would go with one more relation table sequences (id, user_id, max_nfeeds_id), but lets stay with your example.

You want to have condition based on previous row but AFAIK MySQL doesn't offer any straight forward solution to do this. But...

You can join record with next id and compare user IDs.

SELECT nfeeds.*
FROM nfeeds
LEFT JOIN nfeeds AS nfeeds_prev ON nfeeds_prev.id = (
    SELECT MIN(id) FROM nfeeds AS nfeeds_inner 
    WHERE nfeeds_inner.id > nfeeds.id)
WHERE nfeeds_prev.userId IS NULL
    OR nfeeds.userId != nfeeds_prev.userId

The IS NULL part is there for case that nfeeds_inner returns NULL (first record).

I doubt you will be able to take advantage of indexes with this and you WILL always end up with Using Where going trough all rows.

Mysql: Group rows with same value until the value is changed

3 Answers3