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.

nirosh

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

Fred

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