why distinct does not work in following queries

[keith23]

Hi all.May i know why distict does not work in switch case below :

switch ($m){
case "title":
$query = "select id,artist,track,album,title from files where title like '%$s%'order by track";
break;
case "album":
$query = "select DISTINCT id,artist,track,album,title from files where album like '%$s%' ";
break;
case "artist":
$query = "select DISTINCT id,artist,track,album,title from files where artist like '%$s%'order by artist";
break;
}

I be happy if an expert help me how to exclude rpeated values from result sets.Thanks

 

[towerbase]

This is a MySQL forum so you should ask your question in terms of MySQL and not complicate the question by including PHP (or whatever) code.

When you say "does not work" can you give a small example of the results obtained and the results you expect?

Can you quote the MySQL documentation that indicates that you are getting the wrong results?

It usually helps to say what version of MySQL you are using.

Andrew
Hampshire, UK

 

[r937]

keith, would it help you to understand how DISTINCT works if i told you that DISTINCT isn't a function, and DISTINCT doesn't operate on columns, it operates on rows?

r937.com | rudy.ca

 

[keith23]

Many thanks for all of you. Here u see an example that produces repeated rows which i do not in query result:

select[b] DISTINCT [/b]id,artist,track,album,title from files where album like '%moon%'
i have one album which is called moon and it has 4 songs on it.This query produced :

 album    artist
 moon     andy   
 moon     andy 
 moon     andy 
 moon     andy

Instead of producing one row it produces 4 rows. I use this query for search by album text box so i do not want repeatd info.I be happy if i get some help here.Thanks

 

[r937]

actually, that result set (with the two columns album and artist) is not what was produced by that query, which actually returns 5 columns (id, artist, track, album, title)

display all 5 columns and you will see that each and every single row displayed is distinct

r937.com | rudy.ca

 

[keith23]

well i did not use all the feilds in my query in printing the result i used 2 of them. Well you are righ those rows are diffrent tracks and diffrent song title. But how i can modify the query so that it produces one row for each unique album in db ? Thanks

 

[r937]

But how i can modify the query so that it produces one row for each unique album in db ?

select DISTINCT album, artist
  from files 
 where album like '%moon%'

r937.com | rudy.ca