SQL Trick: Selecting unique records.
The Situation:
I have a bunch of records. They are items with five or so fields. One of the fields is the artist that created the item (artist_id). I needed to pull just one item from each artist and I needed all the other fields associated with each item. I tried:
SQL:
-
SELECT DISTINCT artist_id FROM items ORDER BY artist_id ASC
This partially did what I wanted. It returns all the UNIQUE artist_id. I want essentially a sample item for EACH artist.
The Solution:
SQL:
-
SELECT *, COUNT(artist_id) FROM items GROUP BY artist_id HAVING COUNT(artist_id)>=1
This returns ONE row for each artist. I found this on a blog, I cant remember the name but when I find it again I will post credit. I hope this helps some of you.