Best way to Page Results for External SQL query

When working with a large query executed though an ODBC connection what is the best way to allow the paging of the results at the client side.  I have tried some methods using %VID and similar methods, but these really don't seem to work as the value returned is related to the ID of the data and not the position in the results set.  What would be ideal is if the value seen in the management portal when you check of "Row Number" was available to external queries through ODBC.  I have not seen a way to return this however.   This would be similar to SQL server Last n and Skip n capabilities.

  • 0
  • 0
  • 406
  • 2
  • 3

Answers

I'm not sure I understand the objection to %VID. I've seen a few different recommendations on how best to use it, but I think it does what you want in the following example:

select *
from (
    select top all *
    from Sample.Person
    order by DOB
) where %vid between 10 and 19

Regardless of the where or order by clause that you put in the sub-query, %VID refers to the position in the result set.

I attempted this with the following query:

SELECT Name,%VID,ID,Age FROM
   (SELECT TOP 10 * FROM Sample.Person )
ORDER BY Name

Here are the results:  You can see that the %VID column does not correspond to the returned result set.

#NameLiteral_2IDAge
1Adam,Tara P.7759
2Brown,Orson X.8892
3Hernandez,Kim J.1123
4Kelvin,Dick J.6612
5Orwell,Andrew T.9946
6Page,Lawrence C.3367
7Quixote,Andrew Q.4473
8Williams,Stuart O.101077
9Yakulis,Fred X.2282
10Zemaitis,Emilio Q.5557

You want to put the order by clause in the sub-query.

The ORDER BY needs to go with the TOP, so your query should be

 

SELECT Name,%VID,ID,Age FROM
   (SELECT TOP 10 * FROM Sample.Person ORDER BY Name) 

 

then it does what you want.