CODEWITHSUNDEEP
phpmysql
scorpio1441
scorpio1441

Reputation: 3088

select distinct and other column in the same row

Is there an easy, non-JOIN way, to perform this task?

mysql_query("select DISTINCT artist, id from ringtones where artletter='A'");

Result should be: all artist values without duplicates with corresponding id.

Upvotes: 0

Views: 80

Answers (2)

Jeremy Smyth
Jeremy Smyth

Reputation: 23493

You can get one row per artist, with all the IDs for that artist on one line by doing this:

SELECT artist, GROUP_CONCAT(id) as `IDs`
FROM ringtones
WHERE artletter='A'
GROUP BY artist;

If you want only the first id for each artist, try this:

SELECT artist, MIN(id) as `first_id`
FROM ringtones
WHERE artletter='A'
GROUP BY artist;

(you could get the last id by using MAX instead of MIN)

If you want the first or last row (based on id) for that artist, you can do a groupwise max as described in that link.

Upvotes: 3

Chella
Chella

Reputation: 1562

Probably you are looking for this..

select DISTINCT artist, id where id IN(select DISTINCT id from ringtones where artletter='A')

Upvotes: 0

Related Questions