MySQL get first row of joined table in group

Question

I have a simple one-to-many relationship. One table is a list of albums, and the other a list of photos. Each photo can only be of one album, but an album contains many images.

Now I want to get a list of all the albums in a single query. The information I want is some values of the album itself (let's say the name of the album), the total image count in that album and some values of the first image.

I already have the total image count and the album name, but I am having some issues trying to get the first image values as well.

SELECT albums.id, albums.name, COUNT(photos.id) AS imageCount 
   FROM albums 
   LEFT JOIN photos ON albums.id = photos.albumid 
   GROUP BY name 
   ORDER BY id DESC

I have tried several approaches from other answers but I seem to be doing something wrong. It's especially confusing since I am using COUNT() as well.

I have created a sqlfiddle that has this query in it as well as a small sample database. So how do I also get some values of the first element of photos?

See http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 for how to get the row with the first image for each album. Then join that with the rest of your query. — Barmar, Jan 21 '16 at 18:18

score 3 · Accepted Answer · answered Jan 21 '16 at 17:35

SELECT x.id
     , x.name
     , total
     , y.id
     , y.url
  FROM 
     ( SELECT a.*
            , COUNT(p.id) total
            , MIN(p.id) min_id
         FROM albums a
         LEFT
         JOIN photos p
           ON p.albumid = a.id
        GROUP
           BY a.id
     ) x
  LEFT
  JOIN photos y  
    ON y.id = x.min_id;

Gouda Elalfy · Answer 2 · 2016-01-21T18:30:54.287

1

you can do by this query:

SELECT albums.id, 
       albums.name, 
       COUNT(photos.id) AS imageCount , 
       (select url 
        from photos 
        where id =(select min(id) 
                   from photos internal_photo 
                   where albums.id = internal_photo.albumid )) as photo_url
FROM albums 
LEFT JOIN photos ON albums.id = photos.albumid 
GROUP BY name 
ORDER BY id DESC

edited Jan 21 '16 at 18:30

answered Jan 21 '16 at 17:03

Gouda Elalfy

6,888
1
26
38

Hope you dont mind the format, you can reverse change if not agree. – Juan Carlos Oropeza Jan 21 '16 at 17:17
Seems to work good! Slight remark though, for use in the sqlfiddle I provided the `url` needs to be selected from photos and not `name`. – jdepypere Jan 21 '16 at 17:20
The selection of url could be simplified as _(SELECT url FROM photos where albumid=albums.id order by 1 limit 1) url_ – PaulF Jan 21 '16 at 17:24
@PaultF You are right, but my experience say `ORDER` are high cost, so is better check the query plan first. `MIN()` can use b-tree index to speed things up. – Juan Carlos Oropeza Jan 21 '16 at 17:27
@JuanCarlosOropeza: adding the additional select may be slower than using ORDER - but as you rightly say it would be worth checking the query plan. – PaulF Jan 21 '16 at 17:35

Juan Carlos Oropeza · Answer 3 · 2016-01-21T17:30:17.400

0

Im using the url format to get the first one, if you have a different format you need another join to get the first one.

SELECT A.*, P.url
FROM 
    (
      SELECT albums.id, albums.name, COUNT(photos.id) AS imageCount 
      FROM albums 
      LEFT JOIN photos ON albums.id = photos.albumid 
      GROUP BY name 
    ) A
LEFT JOIN (
        SELECT `albumid`, MIN(`url`) as `url`
        FROM photos
        GROUP BY `albumid`
    ) P
  ON A.id = P.`albumid`
ORDER BY id DESC

A more generic version free of string hassles would be:

SQL Fiddle Demo

SELECT *
FROM 
    (
      SELECT albums.id, albums.name, COUNT(photos.id) AS imageCount 
      FROM albums 
      LEFT JOIN photos ON albums.id = photos.albumid 
      GROUP BY name 
      ORDER BY id DESC
    ) A
LEFT JOIN (
        SELECT photos.`albumid`, photos.`url`
        FROM photos
        LEFT JOIN (
                SELECT `albumid`, MIN(`id`) as `id`
                FROM photos p1
                GROUP BY `albumid`
              ) P1
          ON photos.`albumid` = p1.`albumid`
         AND photos.`id`  = P1.`id`
    ) P
  ON A.id = P.`albumid`

edited Jan 21 '16 at 17:30

answered Jan 21 '16 at 16:52

Juan Carlos Oropeza

47,252
12
78
118

Why the downvote?. Please leave a comment if you see anything wrong – Juan Carlos Oropeza Jan 21 '16 at 17:14
It wasn't me that downvoted - but this depends on a string comparison involving strings with variable length number - so if the first was photo1_2.png & the last was photo1_100.png - then your query would return photo1_100.png – PaulF Jan 21 '16 at 17:17
@PaulF I know that is why I put that comment on the first place. I guess should add the other version. – Juan Carlos Oropeza Jan 21 '16 at 17:18
I personally went for an upvote, not sure who the downvote is coming from. Seems to work fine, I would however prefer to also retrieve albums that are empty, so I changed the `JOIN` to a `LEFT JOIN`. I would also prefer getting the first one based on the id instead of the name, as @PaulF mentioned. – jdepypere Jan 21 '16 at 17:18
@jdepypere Both are right, I just saw a shortcut and took it. Will fix it. – Juan Carlos Oropeza Jan 21 '16 at 17:19
@JuanCarlosOropeza thanks for the fix! Any ideas about performance diffs with Gouda's solution? Since it has a join less I would assume his might be a tad more performant? – jdepypere Jan 21 '16 at 17:33
I personally dont like the subquery in the `SELECT` feel like every row has to do a table scan, even when I know db do some optimization. But you can check the query plan on sqlfiddle and compare. – Juan Carlos Oropeza Jan 21 '16 at 17:35
Not my dv, but there is a bit more query here than is strictly neccesary – Strawberry Jan 21 '16 at 17:35
@Strawberry Yes your solution is better, put two in the same query – Juan Carlos Oropeza Jan 21 '16 at 17:39

score 0 · Answer 4 · answered Jan 21 '16 at 17:32

0

If you use MySQL as your database, you can run this query

SELECT albums.id, albums.name, photos.url, COUNT(photos.id) AS imageCount
FROM albums 
LEFT JOIN photos ON albums.id = photos.albumid 
GROUP BY name 
ORDER BY id DESC

Because Grouping in MySQL always return the first row value. CMIIW. But you can also try this query (only a nested subquery).

SELECT albums.id, albums.name, COUNT(photos.id) AS imageCount, (
    SELECT photos.url 
    FROM photos 
    WHERE albums.id = photos.albumid 
    ORDER BY id ASC 
    LIMIT 1
)
FROM albums 
LEFT JOIN photos ON albums.id = photos.albumid 
GROUP BY name 
ORDER BY id DESC

answered Jan 21 '16 at 17:32

Wilianto Indrawan

2,394
3
21
26

I am indeed running MySQL and did not know that was default behavior. Thanks! – jdepypere Jan 21 '16 at 17:36
1

`id` is ambiguous on your `ORDER`. MySql isnt really ANSI regarding the `GROUP BY` and even when may work isnt a guarantee. – Juan Carlos Oropeza Jan 21 '16 at 17:37
I don't think MySQL guarantees that grouping will always return columns from the first row in the order. – Barmar Jan 21 '16 at 17:57
@Barmar The manual is explicit on this point (https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html) - although I have to admit that I've not yet encountered a situation where it didn't – Strawberry Jan 21 '16 at 18:11
1

Right, the manual says: **The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause.** – Barmar Jan 21 '16 at 18:16
@Barmar I'm also not sure about that. Thank you for correcting me. – Wilianto Indrawan Jan 21 '16 at 18:27
If it sometimes works, it might be dependent on the storage engine and/or the query plan. – Barmar Jan 21 '16 at 18:28

MySQL get first row of joined table in group

4 Answers4