Identify the last row of a distinct set of data in the field for an Alias ​Column

Question

How can i identify the last Row of a distinct set of data in the field for an Alias ​​Column (signaling somehow, with "1" for example).

For this example i need to know, when the ordered GROUP "CARS, COLORS, DRINKS, FRUITS" ends.

Check my intended result on this image:

My base query:

SELECT * FROM `MY_DB` ORDER BY `ITEM`, `GROUP` ASC

Answer 1

As a starter: rows of a SQL table are unordered. There is no inherent ordering of rows. For your question to make sense, you need a column that defines the ordering of the rows in each group - I assumed id.

Then: in MySQL 8.0, one option uses window functions:

select t.*,
    (row_number() over(partition by grp order by id desc) = 1) as last_group_flag
from mytable t

In earlier versions, you could use a subquery:

select t.*,
    (id = (select max(t1.id) from mytable t1 where t1.grp = t.grp)) as last_group_flag
from mytable t

Note: group is a language keyword, hence not a good choice for a column name. I used grp instead in the query.