mysql - How can I get the first result from a subquery?

Question

I have two tables. Here is a simplified breakdown:

Table #1 - Album:
Rows:
albumId | title | userId

Table #2 - Photo:
Rows:
photoId | src | albumId

I want to get the first photo's src from each album. This is pretty clearly not what I'm looking for but here is what I have:

SELECT pa.id, pa.title, p.src
FROM Album pa
LEFT JOIN Photo p ON pa.Id = p.albumId
WHERE pa.userId = 1

That returns all of the photos from the user. I would like the first result for each album in those results.

If you want to retrieve the albums' covers - you better add the separated column for that, like `cover_photo_id` and have it specified explicitly — zerkms, Apr 09 '12 at 23:47
pa.id doesn't exist, it should be pa.albumId ;) just so that other repliers keep that in mind — g3rv4, Apr 10 '12 at 00:14

g3rv4 · Accepted Answer · 2012-04-10T00:14:05.910

3

I think you may want to add a cover_photo_id, as @zerkms said, but this could do the trick (don't know if using subqueries is efficient enough for your situation)

SELECT pa.albumId, pa.title, p.src
FROM Album pa
LEFT JOIN Photo p 
  ON p.photoId = (SELECT MIN(photoId) FROM Photo WHERE albumId = pa.albumId)
WHERE pa.userId = 1

edited Apr 10 '12 at 00:14

answered Apr 09 '12 at 23:47

g3rv4

19,750
4
36
58

Nope, this would return only one row at all – zerkms Apr 09 '12 at 23:48
2

You better put that condition to the `ON` part – zerkms Apr 09 '12 at 23:54

score 0 · Answer 2 · edited Apr 10 '12 at 14:25

0

SELECT pa.id, pa.title, p.src
FROM Album pa
  LEFT JOIN Photo p ON pa.Id = p.albumId
WHERE pa.userId = 1 LIMIT 1;

edited Apr 10 '12 at 14:25

kba

19,333
5
62
89

answered Apr 09 '12 at 23:48

bad_cab

1

MySQL Error: "This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'" – HoldOffHunger Sep 17 '18 at 18:26

score -1 · Answer 3 · edited May 23 '17 at 12:06

-1

You want to use GROUP BY

SELECT pa.id, pa.title, p.src
FROM Album pa
LEFT JOIN Photo p ON pa.Id = p.albumId
WHERE pa.userId = 1
GROUP BY pa.id, pa.title

If you actually want the equivalent of a FIRST, see this answer

edited May 23 '17 at 12:06

Community

1
1

answered Apr 09 '12 at 23:49

pocketfullofcheese

8,427
9
41
57

And what `p.src` value is supposed to be selected? – zerkms Apr 09 '12 at 23:50
1

Haha, nice. But it's not April 1st :) – ypercubeᵀᴹ Apr 09 '12 at 23:51
2

I'd better go with another magic function `SELECT WHATEVERINEED()` – zerkms Apr 09 '12 at 23:51
if you leave it, it'll just grab the first record – pocketfullofcheese Apr 09 '12 at 23:52
@pocketfullofcheese: there is no `first` or `last` row until you define an order – zerkms Apr 09 '12 at 23:53
And there is no [`FIRST()`](http://dev.mysql.com/doc/refman/5.5/en/func-op-summary-ref.html) function in MySQL. – ypercubeᵀᴹ Apr 09 '12 at 23:54
I don't think the OP actually cares about it being the first. He just wants one. So ordering however he wants that last column should do the trick. – pocketfullofcheese Apr 09 '12 at 23:57
@pocketfullofcheese: if he wanted `any` he would probably say `any`. But he said `first`. It is not a justification to the wrong query (it is wrong at least from ANSI SQL perspective) – zerkms Apr 09 '12 at 23:58
Just gave him a link for how to emulate a FIRST for that last column – pocketfullofcheese Apr 09 '12 at 23:59
@pocketfullofcheese: `MIN` or `MAX` used with `p.src` wouldn't give first or last row. They would return the least or greatest `p.src`, which is obviously a URL – zerkms Apr 10 '12 at 00:02

score -2 · Answer 4 · answered Apr 10 '12 at 00:01

-2

SELECT pa.id, pa.title, p.src
FROM Album pa
LEFT JOIN Photo p ON pa.Id = p.albumId
WHERE pa.userId = 1
GROUP BY pa.albumId
ORDER BY p.photoID

answered Apr 10 '12 at 00:01

Ata S.

890
7
12

you can't get fields that aren't aggregates or that are on the group key... can you? – g3rv4 Apr 10 '12 at 00:05
2

@elgerva: Actually you can in MySQL, but the returned data (for `p.src`) is undefined. It can be from any (random) row in the group. – ypercubeᵀᴹ Apr 10 '12 at 00:07
Yep you can in MySQL. MySQL shouldn't do that I agree. I don't know if it is always the expected row, I just checked a few results and it is fine. Seems like working to me. – Ata S. Apr 10 '12 at 00:15

mysql - How can I get the first result from a subquery?

4 Answers4