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.
Comments
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.
| # | Name | Literal_2 | ID | Age |
|---|---|---|---|---|
| 1 | Adam,Tara P. | 7 | 7 | 59 |
| 2 | Brown,Orson X. | 8 | 8 | 92 |
| 3 | Hernandez,Kim J. | 1 | 1 | 23 |
| 4 | Kelvin,Dick J. | 6 | 6 | 12 |
| 5 | Orwell,Andrew T. | 9 | 9 | 46 |
| 6 | Page,Lawrence C. | 3 | 3 | 67 |
| 7 | Quixote,Andrew Q. | 4 | 4 | 73 |
| 8 | Williams,Stuart O. | 10 | 10 | 77 |
| 9 | Yakulis,Fred X. | 2 | 2 | 82 |
| 10 | Zemaitis,Emilio Q. | 5 | 5 | 57 |
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.
Else can use the class %ScrollableResultSet