1

I have a table that has columns for id,category and position:

id | cat | pos
--------------
01 | ct1 | 2
02 | ct1 | 3
03 | ct2 | 1
04 | ct1 | 1
05 | ct2 | 2

I want to select the rows with the lowest position from each category. So from this data I should get id's 03 and 04 since they are position 1 from each respective category.

I have seen similar posts but with slight twists that make it hard to translate the knowledge, for example one suggested solution only has two groups so they have a fixed UNION as the selected answer which is way off for me as categories can grow. A decent explanation would be nice as I do not understand why the following doesn't work.

SELECT id,pos,title,cat,des,url,urltext 
FROM gallery_items
GROUP BY cat
ORDER BY pos ASC
Rafael
  • 7,605
  • 13
  • 31
  • 46
webfish
  • 80
  • 8
  • Possible duplicate of [Get top n records for each group of grouped results](http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) – shmosel Jan 06 '17 at 05:43
  • Please Tag Database – xQbert Jan 06 '17 at 16:37

1 Answers1

2

One approach uses a join to a subquery which finds the minimum position for each category:

SELECT t1.*
FROM gallery_items t1
INNER JOIN
(
    SELECT cat, MIN(pos) AS min_pos
    FROM gallery_items
    GROUP BY cat
) t2
    ON t1.cat = t2.cat AND
       t1.pos = t2.min_pos

Another way to do this would be via a correlated subquery:

SELECT t1.*
FROM gallery_items t1
WHERE t1.pos = (SELECT MIN(pos) FROM gallery_items t2 WHERE t1.cat = t2.cat)
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Can you explain or link me to where I can read on which is faster and why? The query works as expected, however, I do not understand why the join/subquery is required. – webfish Jan 06 '17 at 15:31
  • the second subquery method does not work. This method is more along the lines of what I would expect logic wise so would be cool to see it working – webfish Jan 06 '17 at 15:33
  • 2nd one needs the from to be `FROM gallery_items t1` the `t1` is vital. – xQbert Jan 06 '17 at 16:38