# DISTINCT field name dosen't give the expected output

**URL:** <https://www.ruby-forum.com/t/distinct-field-name-dosent-give-the-expected-output/188983>\
**Category:** Rails\
**Created:** [May 21, 2010, 12:33pm UTC](https://www.ruby-forum.com/t/distinct-field-name-dosent-give-the-expected-output/188983 "2010-05-21T12:33:09Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![nirosh](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/ce7236/32.png) [@nirosh](https://www.ruby-forum.com/u/nirosh)\
**Post date:** [May 21, 2010, 12:33pm UTC](https://www.ruby-forum.com/t/distinct-field-name-dosent-give-the-expected-output/188983/1 "2010-05-21T12:33:09Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![nirosh](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/ce7236/32.png) [@nirosh](https://www.ruby-forum.com/u/nirosh)\
**Post date:** [May 21, 2010, 1:27pm UTC](https://www.ruby-forum.com/t/distinct-field-name-dosent-give-the-expected-output/188983/2 "2010-05-21T13:27:35Z")

</div>

On May 21, 11:32 am, nirosh [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> 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

---

<div class="post-metadata">

**Author:** ![nirosh](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/ce7236/32.png) [@nirosh](https://www.ruby-forum.com/u/nirosh)\
**Post date:** [May 21, 2010, 3:42pm UTC](https://www.ruby-forum.com/t/distinct-field-name-dosent-give-the-expected-output/188983/3 "2010-05-21T15:42:13Z")

</div>

yes works great the query now i use is SELECT \* FROM lyrics WHERE  
movie\_name like ‘a%’ GROUP BY Movie\_name