Add a (semi-)sequential number to rows based on group by and ID, then mark newest two items

Question

I want to do (somewhat) the opposite of what this old post was about;

Here's my example table:

ID   NAME    
---- ----
1    Apple
2    Apple
3    Apple
4    Bravo
5    Bravo
6    Cat
7    Cat
8    Cat
9    Cat
10   Apple
11   Apple

I want to add a new row that adds a sequential number for each group of items. The problem is that some items appear multiple times, but don't belong to the same group. They only belong together if they appear right after one another (sequential ID).

The updated table should look like this:

ID   GROUP NAME    
---- ----- ----
1    1     Apple
2    1     Apple
3    1     Apple
4    2     Bravo
5    2     Bravo
6    3     Cat
7    3     Cat
8    3     Cat
9    3     Cat
10   4     Apple
11   4     Apple

And then as a last step, I want to separately mark the newest two items of each group (the ones with the highest ID value), kind of like this:

ID   GROUP NAME   MARK    
---- ----- ----   ----
1    1     Apple  false
2    1     Apple  true
3    1     Apple  true
4    2     Bravo  true
5    2     Bravo  true
6    3     Cat    false
7    3     Cat    false
8    3     Cat    true
9    3     Cat    true
10   4     Apple  true
11   4     Apple  true

How can I do this in MySQL?

To assign a group use variable that is being conditionally incremented via in-line assignment. For the second task - note that last two become first two if you reverse your order. — PM 77-1, Apr 10 '17 at 19:58

Juan Carlos Oropeza · Accepted Answer · 2017-04-10T20:09:32.210

Using variables, First you create the groups order by id and name, Then sorting those grp for id, you mark the first two.

SQL DEMO

SELECT `ID`, `NAME`, rn,
        mark <= 2 as mark       
FROM (
        SELECT `ID`, `NAME`, rn, 
               @mark := IF(@grp = rn,
                           @mark + 1,
                           IF( @grp := rn, 1, 1)                                      
                           ) as  mark
        FROM (       
                SELECT `ID`, `NAME`, 
                       @rn := IF(@name = `NAME`,
                                 @rn,
                                 IF(@name := `NAME`, @rn + 1, @rn + 1)
                                ) as rn
                FROM Table1
                CROSS JOIN (SELECT @rn := 0, @name :='') var
                ORDER BY `ID`
             ) T
        CROSS JOIN (SELECT @mark := 0, @grp := 0 ) var
        ORDER BY rn, `ID` DESC
    ) Y
ORDER BY ID

OUTPUT

Add a (semi-)sequential number to rows based on group by and ID, then mark newest two items

1 Answers1

Linked