mysql. sql query. selecting

there is a table with columns: id, artist, title, album. i need to get rows with different values of pair (artist, title) and album

example:

i have now (result of my开发者_Python百科 query):

===artist ======= title ========= album

some artist1 === song title 1 === first album

some artist1 === song title 1 === second album

some artist1 === song title 1 === third album

i need:

===artist ======= title ========= album

some artist1 === song title 1 === first album

SELECT  t.*
FROM    (
        SELECT  DISTINCT artist, title
        FROM    mytable
        ) td
JOIN    mytable t
ON      t.id = 
        (
        SELECT  id
        FROM    mytable ti
        WHERE   (ti.artist, ti.title) = (td.artist, td.title)
        ORDER BY
                ti.artist, ti.title, ti.id
        LIMIT 1
        )

This will return first album for a given (artist, title) in id order.

Change ORDER BY condition in the subquery to control which album will be returned.

Use group by as

select * from `table` group by title,artist;

It will return one row for one pair of title and artist.

Filter out second and third album. WHERE album NOT IN ('second album','third album')

It helps though if we can see schema.

Which album you choose to show, depends on the ordering of albums. That ordering is not unique, but something like the following could produce what you are looking for:

SELECT artist, title, MAX(album)
FROM MyTable
GROUP BY artist, title

Try this:

GROUP BY artist title album