Board index » delphi » SQL question: pick record from group by criteria

SQL question: pick record from group by criteria

This is a situation I've encountered a couple of times, and I haven't
found a solution, but worked around it instead. Say I have two tables,
Albums and Songs. Each Album record can "own" any number of Song
records. Each Song has a length field in seconds.

Now, say I want to select the name of the longest song of each album.
How do I do that?

I'd like to do something like this ("pseudo" SQL - doesn't work of
course):

SELECT A.Title, S.Title FOR MAX(S.Length)
FROM Albums A
  INNER JOIN Songs S
    ON A.AlbumId=S.AlbumId
GROUP BY A.AlbumId
ORDER BY A.Title

I'd like the FOR construct to give me S.Title for the S record implied
by MAX(S.Length).

The best solution (i.e. the only solution) I've found is to create a
view selecting Albums.AlbumId and MAX(Songs.Length) for each album, and
then joinging with that view:

CREATE VIEW MaxSongLengths AS
SELECT AlbumId AlbumId, MAX(Length) Length
FROM Songs
GROUP BY AlbumId

SELECT A.Title Album, S.Title Song
FROM Albums A
  INNER JOIN Songs S
    ON A.AlbumId=S.AlbumId
  INNER JOIN MaxSongLengths M
    ON S.AlbumId=M.AlbumId AND S.Length=M.Length

But I'd like something simpler - e.g. something which doesn't require a
view. Any ideas?

Thanks,
Kjell Rilbe

 

Re:SQL question: pick record from group by criteria


Quote
Kjell Rilbe wrote in message <38A42742.C5CFC...@upec.se>...

>SELECT A.Title, S.Title FOR MAX(S.Length)
>FROM Albums A
>  INNER JOIN Songs S
>    ON A.AlbumId=S.AlbumId
>GROUP BY A.AlbumId
>ORDER BY A.Title

Untested but this should work.

SELECT A.AlbumId, A.Title, S.Title, MAX(S.Length)
FROM Albums A
  INNER JOIN Songs S
    ON A.AlbumId=S.AlbumId
GROUP BY A.AlbumId, A.Title, S.Title
ORDER BY A.Title, S.Title

If not then the View is your best option.

--
Wayne Niddery - WinWright Consulting
RADBooks - http://members.home.net/wniddery/
You have a Right to Free Speech, but not the right to make me listen, nor to
use my property as a soapbox.

Re:SQL question: pick record from group by criteria


Thanks Wayne, but it doesn't work. It groups by S.Title, and so will
select all songs (with unique titles). If there are two songs with
identical titles on the same album, the length of the longest one will
be returned. But I wanted only one song from each album, so S.Title
can't be in the GROUP BY. Nice try though!

Kjell

"Wayne Niddery (TeamB)" skrev:

Quote

> Kjell Rilbe wrote in message <38A42742.C5CFC...@upec.se>...

> >SELECT A.Title, S.Title FOR MAX(S.Length)
> >FROM Albums A
> >  INNER JOIN Songs S
> >    ON A.AlbumId=S.AlbumId
> >GROUP BY A.AlbumId
> >ORDER BY A.Title

> Untested but this should work.

> SELECT A.AlbumId, A.Title, S.Title, MAX(S.Length)
> FROM Albums A
>   INNER JOIN Songs S
>     ON A.AlbumId=S.AlbumId
> GROUP BY A.AlbumId, A.Title, S.Title
> ORDER BY A.Title, S.Title

Re:SQL question: pick record from group by criteria


I think something like this will work:

select A.title as album, S.Title as song, S.Lth
from Album A, Song S
where A.Albumid = S.Albumid
and S.Lth = (select max(Lth) from Song S2 where S2.Albumid = A.Albumid)

--
Christopher Latta  http://www.ozemail.com.au/~clatta
  It is February 2000 - Do you know where your Messiah is?

Quote
Kjell Rilbe wrote in message <38A42742.C5CFC...@upec.se>...
>This is a situation I've encountered a couple of times, and I haven't
>found a solution, but worked around it instead. Say I have two tables,
>Albums and Songs. Each Album record can "own" any number of Song
>records. Each Song has a length field in seconds.

>Now, say I want to select the name of the longest song of each album.
>How do I do that?

8< -- snip --

Re:SQL question: pick record from group by criteria


Christopher,

Yes, that seems to work! Thanks! But it still requires a subquery. Guess
it can't be done without one, eh? Oh well...

Kjell Rilbe

Christopher Latta skrev:

Quote

> I think something like this will work:

> select A.title as album, S.Title as song, S.Lth
> from Album A, Song S
> where A.Albumid = S.Albumid
> and S.Lth = (select max(Lth) from Song S2 where S2.Albumid = >   A.Albumid)

> --
> Christopher Latta  http://www.ozemail.com.au/~clatta
>   It is February 2000 - Do you know where your Messiah is?

> Kjell Rilbe wrote in message <38A42742.C5CFC...@upec.se>...
> >This is a situation I've encountered a couple of times, and I haven't
> >found a solution, but worked around it instead. Say I have two
> >tables, Albums and Songs. Each Album record can "own" any number of
> >Song records. Each Song has a length field in seconds.

> >Now, say I want to select the name of the longest song of each album.
> >How do I do that?
> 8< -- snip --

Other Threads