1

I want to retrive only a row from a N row result of a SQL query. For example:

Set rs = conn.Execute("SELECT ..... ")
strResult = rs.Fields(0)

Query result:

30
45
70
....

How can i use the second value (20) from this query for example? Either by VBA or altering the sql query. I'm using SQL server. There are the "LIMIT" or "TOP" commands but they are for a different purpose.

Community
  • 1
  • 1
Bruno Lopes
  • 169
  • 7
  • 20

2 Answers2

0

This may helps you OFFSET n ROWS means after n rows FETCH NEXT 1 ROWS ONLY means getting one row but it requires ORDER BY statement 

SELECT * from table
ORDER BY <columns>
OFFSET n ROWS
FETCH NEXT 1 ROWS ONLY
koushik veldanda
  • 1,079
  • 10
  • 23
0

You can use ROW_NUMBER() function. You will have to select ROW_NUMBER over some ordered column and then you can query your selection using WHERE cla

USE AdventureWorks2012; 
GO
SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row, 
    FirstName, LastName, ROUND(SalesYTD,2,1) AS "Sales YTD" 
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;

Use common table expression to query your ROW Number

WITH OrderedEmployees(Row, FirstName, LastName)
As(
    SELECT ROW_NUMBER() OVER(ORDER BY FirstName DESC) AS Row, 
        FirstName, LastName
    FROM Employees
) 
SELECT * FROM 
OrderedEmployees
WHERE Row = 2

You can check out this SQL FIDDLE and validate your query against it.

Using Common Table Expressions

ROW_NUMBER (Transact-SQL)

Sameer Azazi
  • 1,503
  • 10
  • 16