+ Start a Discussion
anuragsinghnsanuragsinghns 

get 1 value per item in List SOQL Query

hi guys,
I was wondering if there is a way I can write a query which would return me one value for a corresponding match in a list.
 Example say this is my set {'one','two','three'} lets call it mynameSet
and I write a query
list<account> myaccounts =[select id,Name from account where name=:mynameSet];

now in my system if I have 4 accounts like this below
                     Name
Account 1-  one
Account 2- two
Account 3- three
Account 4-  one

I want my query to return only three records not all 4 it should pick a either account1 or account4 I was also wondering if this is not possible to do so in a query what would be the best way to solve this issue without having the SOQL query return large amount of records.

 
Best Answer chosen by anuragsinghns
bob_buzzardbob_buzzard
You can do this with a GROUP BY clause, but you won't get accounts back - instead you will get AggregateResult records. E.g.
List<AggregateResult> results=[select Name from Account group by name];
In my dev org I have 59 accounts with some duplicate names, and the results of this query are the 46 unique names. 

All Answers

bob_buzzardbob_buzzard
You can do this with a GROUP BY clause, but you won't get accounts back - instead you will get AggregateResult records. E.g.
List<AggregateResult> results=[select Name from Account group by name];
In my dev org I have 59 accounts with some duplicate names, and the results of this query are the 46 unique names. 
This was selected as the best answer
anuragsinghnsanuragsinghns
Thanks Bob  needed the account id's:)  guess will have to write more code to get them if you add the id field to the query then it has to be added as a part of the group by as well and that returns all records obviously. 
Thanks again for all your Help!