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 -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Frederick Cheung
2010-May-21 11:26 UTC
Re: DISTINCT field name dosen''t give the expected output
On May 21, 11:32 am, nirosh <kunalan.kand...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 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 Fred -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
yes works great the query now i use is SELECT * FROM lyrics WHERE movie_name like ''a%'' GROUP BY Movie_name -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.