MySQL query to get either the first or last distinctive entry with all remaining fields per line

Question

Example Table:

id	groupId	data
1	2	apple
2	5	cinnemon
3	5	bacon
4	5	pickles
5	5	wonka
6	42	bar
7	42	linux
8	69	rocks
9	69	joe
10	69	bob
11	101	briggs
12	101	kinky_horror

First Quuery to get data by first unique(groupId) should yield this:

id	groupId	data
1	2	apple
2	5	cinnemon
6	42	bar
8	69	rocks
11	101	briggs

Second Query to get data by **last **unique(groupId) should yield this:

id	groupId	data
1	2	apple
5	5	wonka
7	42	linux
10	69	bob
12	101	kinky_horror

I can probably do this programmatically but rather create a clean query and let mysql do all the grunt work. I read the manual on the distinctive() command but didn't see anyway to specify placement.

Code wise my logic would be select all to grab the entire dataset. Then in python step from 1 onward. If groupId == last groupId, skip, else if not then last was end and current is first of the next distinctive set. Then some extra code for very first and last of entire set. .

I'm at a complete loss if this is logically possible in MySQL as distinctive doesnt seem to allow for nth placement conditions.

Answer 1

Everything you need is row_number().

Query 1:

select id, groupId, data
from (
  select id, groupId, data, 
         row_number() over (partition by groupId order by id) rn
  from horror) t
where rn = 1

Query 2 (descending order):

select id, groupId, data
from (
  select id, groupId, data, 
         row_number() over (partition by groupId order by id desc) rn
  from horror) t
where rn = 1

dbfiddle demo

Answer 2

This is an other way to do it using group by and the aggregate functions min() and max() :

Use min() to find the first records per groupId:

select h.*
from horror h
inner join (
  select min(id) as first_id
  from horror
  group by groupId
) as s on s.first_id = h.id

Use max() to find the last records per groupId:

select h.*
from horror h
inner join (
  select max(id) as last_id
  from horror
  group by groupId
) as s on s.last_id = h.id

Demo here