0

Here is the query:

SELECT c.*, f.file
FROM `category` c
LEFT JOIN
(
    SELECT `file`, catId
    FROM `file`
    ORDER BY sortId
) f
    ON f.catId = c.catId
GROUP BY c.catId
ORDER BY c.sortId

This used to work fine until a mysql update. I have been circling on this for a while now and I can't seem to find a solution.

Here is the last option I tried:

SELECT *
FROM `file`
LEFT JOIN `category`
    ON `file`.catId = `category`.catId
GROUP BY `file`.catId
ORDER BY `category`.sortId

Any guidance would be appreciated.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
Kevin J
  • 194
  • 1
  • 11
  • 1
    I don't even understand why you are using `GROUP BY`. Please add sample input and output data to your question. – Tim Biegeleisen Nov 15 '18 at 00:04
  • @Barmar, I'm not convinces any answer on the duplicate page sufficiently attempts to resolve assumed/implicit ordering of sub-queries to gain a specific result in the top query. – danblack Nov 15 '18 at 00:54
  • 1
    @danblack What implicit ordering? Even before the `only_full_group_by` change, it wouldn't return any specific row in the group. But I've added another dupe for finding the maximum row in each group in case that's what he thought it was doing. – Barmar Nov 15 '18 at 00:57

1 Answers1

0

Using windowing you can get the highest rank. (based off this)

SELECT r.*
FROM (
    SELECT c.*, f.file, ROW_NUMBER() OVER (PARTITION BY catId ORDER BY sortId) AS rank
    FROM `file` f
    LEFT JOIN category c
        ON c.catId = c.catId
    ORDER BY f.catId
) AS r
WHERE r.rank = 1 
ORDER BY catId
danblack
  • 12,130
  • 2
  • 22
  • 41