March 27, 2002 at 9:49 am
Hi,
I have a table called T_Adress. There are 90000 adresses in it.
In my VisualBasic-Program I want to fill a grid with the first 100 hundred. That's very easy. (SELECT Top 100 * FROM T_Adress)
But when the user is scrolling down in the grid I have to get the Columns 101-200, or 201-300, etc.
So my question is, is there any SQL-Statement which gets me the rows 101-200 like the Top 100 does with the first 100? If there is no statement, is there anyway to do it in TSQL?
Right know I read the whole table into a recordset and count myself through it to get the proper rows. That's very slow.
Oh, by the way. The Adress-Query should be ordered by Name1 and City.
I tried it already this way, but it's much slower than getting the whole recordset and counting through.
SELECT TOP 100 * FROM T_Adress
LEFT JOIN
(SELECT TOP 200 ID FROM T_Adress ORDER BY Name1, City) as Ad2
ON T_Adress.ID=Ad2.ID
WHERE Ad2.ID IS NULL
ORDER BY Name1, City
Do you have any idea??
Please help.
Thanks.
Thorsten
March 27, 2002 at 10:53 am
This should do the trick.
Just replace numberstartat with the number to start at.
However I would look at possibly doing a client side cursor to retireve the data as well.
SELECT TOP 100 * FROM T_Adress tout
WHERE (numberstartat-1) < (SELECT COUNT(*) FROM T_Adress tin WHERE tin.Name1 > tout.Name1 AND tin.City > tout.City)
ORDER BY Name1, City
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 03/27/2002 10:53:36 AM
March 27, 2002 at 11:10 am
is the top 100 ordered? If so, then add a where clause that is > than the value in row 100.
Steve Jones
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply