Working with data 13

Distinct

Suppose we want a list of all of the artists whose albums we own and who came out with one

of those albums in the seventies.

select artist from albums where year between 1970 and 1979 order by artist

You'll see a lot of duplicate data. If we were displaying this on a web page, or importing it

into  some  other  document,  we  probably  would  not  want  the  same  artist  listed  multiple

times. This is what the distinct keyword is for.

select distinct artist from albums where year between 1970 and 1979 order by

artist

 Distinct  applies to all of the fields selected. It throws out any records that are exactly like a

previous record. If we choose both artist and year, we will start seeing duplicate artists again,

because the row is not duplicated when the same artist has albums in different years.

select distinct artist, year from albums where year between 1970 and 1979

order by artist, year

It will, however, remove duplicate rows, where the artist came out with more than one album

in a single year. If you remove distinct from the statement, you'll see two entries for Alice

Cooper in 1971. With distinct, there will be only one.

Concatenating items

You can combine multiple fields, or combine a field with some static text, using the concat

function.

select concat(album, " by ", artist) from albums order by album

The concat() function takes a comma delimited list of items and glues them together. Here,

we glued together the album name, a comma and a space, and the artist name.