0

Before mySQL 5.7.5, I was able to figure out this type of queries, but after 5.7.5, because of the option sql_mode=only_full_group_by being enable be default, i'm kind of lost...

I have a query and I want to pick up the first row of each group:

SELECT
    w.work_id, w.`name`, CONCAT(r.recording_id, '/', r.seo_url)
FROM
    work w
    JOIN recording r ON w.work_id = r.work_id
    JOIN `release`rl ON r.release_id = rl.release_id 
WHERE
    r.is_performer = 1 and r.is_video = 0
ORDER BY
    w.work_id, rl.released_date_year is null, rl.released_date_year, rl.released_date_month is null, rl.released_date_day is NULL, rl.released_date_day

result set looks like

| work_id | name     | href             |
|---------|----------|------------------|
|       1 | Not so   | 1/not-so         |
|       2 | The moon | 2/the-moon       |
|       3 | Why      | 3/why.           |
|       4 | A flower | 4/a-flower       |
|       5 | Hello    | 46/hello-world   |
|       5 | Hello    | 56/hello         |
|       6 | A ti     | 7/a-ti           |
|       7 | I know   | 8/i-know         |
|       8 | Because  | 9/because        |
|      12 | Morning  | 23/morning-sun   |
|      12 | Morning  | 84/morning-remix |
|      12 | Morning  | 73/morning-beat  |
|      15 | Saturday | 87/i-know        |
|      16 | Night    | 92/because       |

I want it to return the following:

| work_id | name     | href             |
|---------|----------|------------------|
|       1 | Not so   | 1/not-so         |
|       2 | The moon | 2/the-moon       |
|       3 | Why      | 3/why.           |
|       4 | A flower | 4/a-flower       |
|       5 | Hello    | 46/hello-world   |
|       6 | A ti     | 7/a-ti           |
|       7 | I know   | 8/i-know         |
|       8 | Because  | 9/because        |
|      12 | Morning  | 23/morning-sun   |
|      15 | Saturday | 87/i-know        |
|      16 | Night    | 92/because       |

In other words, I've already sorted the row the way I want it, know I just need to pick the first row of each group.

How do I achieve that?

NOTE: There is something similar but with no answer MySQL 5.7.5+ get first row for the groups

Marco
  • 2,687
  • 7
  • 45
  • 61
  • What was your working query before 5.7? – FanoFN Jan 11 '20 at 05:56
  • In 5.7 you could use row number simulation to pick the first for each group but I don't see anything which could be termed a group in your output data - it would help if you added sample data from the 3 source tables – P.Salmon Jan 11 '20 at 06:47

1 Answers1

0

You can try something like this

SELECT w.work_id, w.`name`, substring_index(GROUP_CONCAT(CONCAT(r.recording_id, '/', r.seo_url)), ',', 1) as href FROM work w JOIN recording r ON w.work_id = r.work_id JOIN `release`rl ON r.release_id = rl.release_id WHERE r.is_performer = 1 and r.is_video = 0 GROUP BY w.work_id ORDER BY w.work_id, rl.released_date_year is null, rl.released_date_year, rl.released_date_month is null, rl.released_date_day is NULL, rl.released_date_day

syed shahab
  • 1
  • 1