I have a table with columns artist, title, album and other columns for dates, ip etc now i want to display unique artist title and album in a row like say,
artist , album, title
a , b , c
a , b , c
d , e , f
a , b , d
then it must display
a,b,c
d,e,f
a,b,d
how?
You can use the DISTINCT keyword.
SELECT DISTINCT artist, title, album
FROM YourTable
But it doesn't seem as though your design is normalised if these are being repeated in each row.
(Edit after comments.) It seems you need other columns but you don't care which of the possible matching values are shown in these. In that case you can use GROUP BY As far as I understand in MySQL if you don't specify selected columns in the GROUP BY you would get this but it's completely invalid in all other RDBMSs.
SELECT artist,
title,
album,
id,
link,
artistlink,
albumlink,
songlength,
ip,
timestamp,
image
FROM wp_air1nowplaying_unique
GROUP BY artist,
title,
album
ORDER BY artist ASC,
album ASC,
title ASC
In other RDBMSs you would need to wrap these in an aggregate. e.g.
SELECT artist,
title,
album,
MAX(id) id,
MAX(link) link,
MAX(artistlink) artistlink,
MAX(albumlink) albumlink,
MAX(songlength) songlength,
MAX(ip) ip,
The values returned could very well be from different rows with the above. To avoid this you would use a greatest-n-per-group query
artist title, album which value should the albumlinks column contain (and ip etc.)?SELECT DISTINCT `artist`, `album`, `cover` FROM `yourtable`;
