Sign Up ›

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

SQL to SOQL

How to write the below code in SOQL format?

 

SQL:-

 

SELECT ROW_NUMBER() 
        OVER (ORDER BY EmployeeName) AS Row, 
    EmployeeId, EmployeeName, Salary 
FROM Employees where Row BETWEEN 2 AND 4
Best Answer chosen by Admin (Salesforce Developers) 
metaforcemetaforce

The following SOQL query returns a result set that skips the first 100 rows of the full query results:


SELECT Name FROM Merchandise__c WHERE Price__c > 5.0 ORDER BY Name LIMIT 50 OFFSET 100

 

similarly, you can use something like:

---------------------------------------------

SELECT EmployeeId, EmployeeName, Salary
FROM Employees
ORDER BY EmployeeName
LIMIT 3
OFFSET 1

---------------------------------------------

Numbers mentioned in the SOQL in the LIMIT and OFFSET clause may vary depending on how BETWEEN operator is interpreted by the database. If the test values are included, then the SOQL will work for you. If test values are excluded, then you may nodify your SOQL to LIMIT 1 as only the record with ROW_NUMBER 3 will be returned in your SQL.

 

Hope this gives you a direction to proceed in.

All Answers

metaforcemetaforce

The following SOQL query returns a result set that skips the first 100 rows of the full query results:


SELECT Name FROM Merchandise__c WHERE Price__c > 5.0 ORDER BY Name LIMIT 50 OFFSET 100

 

similarly, you can use something like:

---------------------------------------------

SELECT EmployeeId, EmployeeName, Salary
FROM Employees
ORDER BY EmployeeName
LIMIT 3
OFFSET 1

---------------------------------------------

Numbers mentioned in the SOQL in the LIMIT and OFFSET clause may vary depending on how BETWEEN operator is interpreted by the database. If the test values are included, then the SOQL will work for you. If test values are excluded, then you may nodify your SOQL to LIMIT 1 as only the record with ROW_NUMBER 3 will be returned in your SQL.

 

Hope this gives you a direction to proceed in.

This was selected as the best answer
Awhina Pouesi-SiakisiniAwhina Pouesi-Siakisini
This doesn't show the row number in the query.  Can it?  If you run the SQL script it actually puts in a column named Row with a row number i.e. 1,2,3,4,5.  How is that done in SOQL?