Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
intern2424intern2424 

Help with a SOQL statement.

I am trying to do a nested SOQL statement. I want to have a query that I order by descending to find the biggest number and then use that number to only find the records with that number. So one part of the nested query will find the largest number and then I will use that number to show only the records that have number.

 

 

I can find the value with this query

 

 

Select Height__c 

from Person__c

Order By Height__c desc limit 1 

 

Just wondering if i can get this value to compare the rest of the records and display the results thanks. 

 

Thanks  

Message Edited by intern2424 on 11-02-2009 07:36 AM
Best Answer chosen by Admin (Salesforce Developers) 
arunkarunk

Hi,

 

 

Yes, you can pass parameters from visualforce pages.

Bind the value to some variable in controller class , say myIntegerVariable.

Then you can use it in query like ..

Person__c person = [Select id from Person__c where height = : myIntegerVariable limit 1]; 

 

If not, as AKall said you will have to use two queries.

Example ...

[Select c.Birthdate From Contact c order by Birthdate nulls last limit 1]

This will give 1923-04-13

Then you can use

[Select id, name from Contact where birthdate > 1923-04-13 ]

 

Unfortunately inner and outer queries cannot be applied on the same object type. 

 

 

Regards,

Arun 

All Answers

AKallAKall

Make sure to specify Nulls Last. I can't remember what the exact syntax is, but if you don't do that then the null values will be at the top of your list and you wont be getting the largest #.

 

 I think you will just have to create another soql query to find the records that have the largest #.
intern2424intern2424
Can I pass that value in from my visual force page.
arunkarunk

Hi,

 

 

Yes, you can pass parameters from visualforce pages.

Bind the value to some variable in controller class , say myIntegerVariable.

Then you can use it in query like ..

Person__c person = [Select id from Person__c where height = : myIntegerVariable limit 1]; 

 

If not, as AKall said you will have to use two queries.

Example ...

[Select c.Birthdate From Contact c order by Birthdate nulls last limit 1]

This will give 1923-04-13

Then you can use

[Select id, name from Contact where birthdate > 1923-04-13 ]

 

Unfortunately inner and outer queries cannot be applied on the same object type. 

 

 

Regards,

Arun 

This was selected as the best answer
marifmarif

 Hi,

 

Select p.Product_Name__c, p.Manufacturer__c, p.Id From Product_List__c p where p.Product_Name__c LIKE \''+searchProdText+'%\' and p.Manufacturer__c in (Select pl.Manufacturer__c From Product_List__c pl where pl.Manufacturer__c LIKE \''+searchProdText+'%\') limit 100

 

 Causes

 

System.QueryException: The inner and outer selects should not be on the same object.

 

I understand the error.

Please let me know how achieve the required result in sales forces.

 

Thanks

Arif
iMasteriMaster

Hi Marif,

 

Have you found how to overcome that problem?
I am facing the same one...

 
SaaniaSaania

Hi iMaster,

 

Other than the solution to use two queries, were you able to resolve this issue?

 

Thanks !