Best way to Page Results for External SQL query

Question

Question

Rich Taylor · Jan 9, 2017

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.

Discussion (5)1

Log in or sign up to continue

Brendan Bannon · Jan 9, 2017

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.

0 0

score 0 · Answer 1 · 2017-01-09T13:40:52-05:00

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.

score 0 · Answer 2 · 2017-01-09T13:56:57-05:00

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

score 0 · Answer 3 · 2017-01-09T14:11:47-05:00

Jon Willeke · Jan 9, 2017

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

0 0

score 2 · Answer 4 · 2017-01-10T04:36:14-05:00

Vitaliy Serdtsev · Jan 10, 2017

Else can use the class %ScrollableResultSet

2 1