lkrasner lkrasner -3 years ago 41
SQL Question

Selecting 1 From Each Group

I have a SQLite table representing a music library called

songs


it looks something like this (there are many more columns, I am simplifying)

title | album | artist
-------|---------|----------
a song | an album| an artist


I am trying to select a list of all albums. Now, on first thought,
SELECT DISTINCT album FROM songs
should work. HOWEVER, this fails if, for example, 2 artists have an album with the same title. Really what I want, is to go through each artist and select 1 of each album therein.

How might I do this?

Answer Source
SELECT album 
FROM (
  SELECT DISTINCT album, artist
  FROM songs
)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download