DISTINCT field name dosen't give the expected output

i used the bellow code to collect the lyrics which are related to particular album and display under the album name.

Lyric.find(:all,:select=>'DISTINCT album_name,title,id,singers',:conditions=> ["album_name LIKE ?", params[:id] + "%"]) But the above sql query is not works for me

the below query is working but this query only returns the album_name not the other fields. Lyric.find(:all,:select=>'DISTINCT album_name',:conditions=> ["album_name LIKE ?", params[:id] + "%"])

is there any way to do this in rails or sql? advise please. thanks in advance.


Distinct applies to the entire row (I'm guess you expected it to just make the album_name distinct). You might try group by album_name - while the standard says that if you do a group by you can't put non grouped by columns in the select clause (except of course when using them with an aggregate function like SUM), some databases let you do this anyway. If so then (for example) the title would be the title of one of the grouped rows, but with no guarantee as to which one. it may not even be the case that if you had two such columns they would be taken from the same row - experiment and look at the documentation for you database


yes works great the query now i use is SELECT * FROM lyrics WHERE movie_name like 'a%' GROUP BY Movie_name