MySQL query: GROUP BY - get specific row (not always the first one)

Question

Table "images" with sample data:

id | user_id | album_id | sort

1     15         1         1
2     15         1         2
3     15         1         0
4     15         2         0
5     15         2         1
6     15         3         0

MySQL Query:

SELECT id, sort, COUNT(*) FROM images WHERE user_id=15 GROUP BY album_id

The query returns one row per unique combination. However, how can you tell which row's id it chooses? E.g. the image with sort=0 is the cover image of an album. The file path to this image contains the id. So, how is it possible to always get the id of the row, where sort=0?

I've tried MIN(sort), which returns always 0 in the sort column, however not the right id for that column...? ORDER BY sort only sorts the results... not a solution either.

Any help is highly appreciated! Thank you very much in advance!

EDIT: hope this will help for better understanding:

The query e.g. gives the following results:

id | sort | count
1     1      3
4     0      2
6     0      1

BUT, how is it possible to get always this specific row, where the value of sort is 0? E.g.

id | sort | count
3     0      3
4     0      2
6     0      1

What is it that you actually want to achieve? Perhaps you could insert a couple of similar tables showing the current and desired results? — Andrew Leach, May 27 '12 at 18:16
@AndrewLeach: thx, please see the updated question with current output and desired output. Hope it is clearer now... — Chris, May 27 '12 at 18:22
Other SQL engines would reject this query because there's a logical problem - `sort` column is not contained in an aggregate function or the `GROUP BY` clause... this is just MySQL being MySQL. See [this question](http://stackoverflow.com/questions/1225144/why-does-mysql-allow-group-by-queries-without-aggregate-functions) and linked materials, as well as [this post](http://weblogs.sqlteam.com/jeffs/archive/2007/07/20/but-why-must-that-column-be-contained-in-an-aggregate.aspx). — DCoder, May 27 '12 at 18:30
@DCoder: thank you for the links! Yeah, that makes sense. Actually I was using the column `sort` in the `SELECT` to show the output of `sort`, in my case it would not make sense to `SELECT` it anyway if it was always `0`. But it is a good point and thx again for that! — Chris, May 27 '12 at 18:35

score 2 · Accepted Answer · answered May 27 '12 at 18:28

2

First, filter the cover images (WHERE sort = 0), then, simply count all images for that album_id.

SELECT id, 
  sort,
  (SELECT COUNT(*) 
   FROM images t 
   WHERE t.album_id = i.album_id) AS count
FROM images i
WHERE user_id = 15
      AND sort = 0

answered May 27 '12 at 18:28

João Silva

89,303
29
152
158

ok, thank you Joao! So there seems to be no solution with only one single select without subselect, I was just wondering if there really wasn't or I was just too blind... :) thank you for your answer! – Chris May 27 '12 at 18:33

score 1 · Answer 2 · answered May 27 '12 at 22:10

There is a way to do this without a correlated subquery:

SELECT id, sort, isum.cnt
FROM images i join
     (select i.album_id, count(*) as cnt
      from images
     ) isum
     on i.album_id = isum.album_id
WHERE user_id = 15 and
      sort = 0

I generally prefer to have all my tables listed in the "from" clause. However, some databases will optimize the correlated subquery better than this form, when there is an index on album_id.

Also, I renamed the third field from "count" to "cnt". It is a very, very bad idea to name columns after SQL reserved words, unless you really have to.

And, finally, most other dialects of SQL support the window function. So, if you change databases, you can do something like

select id, sort, count(*) over (partition by album_id)
from images
where user_id = 15 and
      sort = 0

MySQL query: GROUP BY - get specific row (not always the first one)

Table "images" with sample data:

MySQL Query:

2 Answers2