Question 193126 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search