SELECT TOP 10 -- then get next 10?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

eselk@surfbest.net - 27 Nov 2007 20:12 GMT

I need to do a query and limit it to returning 10 rows at a time. I
can't rely on dynamic cursors or anything like that because I can't
keep a cursor open between each query.

I know I can use "SELECT TOP 10" in my SQL statement to get the first
10. But after that, is there any easy way to get rows 11 to 20 (the
next 10)?

If my query had a simple ORDER BY I know one way I could solve the
problem. For example, if it was "ORDER BY ID", then I would just
include "WHERE ID > X" (X would be the highest ID returned in the
previous query). However, my ORDER BY includes 4 fields, so I think I
would need something pretty messy like this:

WHERE FIELD1 > X OR (FIELD1 = X AND FIELD2 > X) OR (FIELD1 = X AND
FIELD2 = X AND FIELD3 > X).... etc... etc...

And I think it would get pretty long to include all of the actual
cases.

I'm writing a winsock server that uses ADO to read from an Access 2000
database and return certain data to clients. I have too many clients
to keep open cursors just incase they request the next 10 records. I
do have the option of switching the database, if there is an easier
way to do this type of query using MSSQL, or a newer version of
Access. I'd prefer to stay with Access or MSSQL though. Let me know
if you need more higher-level details.

Reply to this Message

DavidB - 27 Nov 2007 20:17 GMT

On Nov 27, 3:12 pm, es...@surfbest.net wrote:

> I need to do a query and limit it to returning 10 rows at a time. I
> can't rely on dynamic cursors or anything like that because I can't
[quoted text clipped - 23 lines]
> Access. I'd prefer to stay with Access or MSSQL though. Let me know
> if you need more higher-level details.

You could always select top 20 and then select the Bottom 10 from that
subset, etc...

Reply to this Message

Rich P - 27 Nov 2007 21:04 GMT

It sounds like you have a web based client application. If you are
trying to run a business with this web client app -- I would seriously
consider stepping up your backend to a server based database. Access is
a micro database system and not well suited for web based business
operations.

For your client app, it sounds like you are using ASP (classic ASP). If
you want to select 10 ordered rows at a time, you will need to add a
column to your backend table's
for ordering. It could be an autonum column. Here is what you could do

If a customer wants to look at a list of books that start with the
letter "B" you can select the top 10 rows from a set where Books Like
'B%'

Select Top 10 * From tbl1 Where Books Like 'B%' Order By RowID

Note: for Best results when using the Top operator -- you need to
include an Order By clause so that the system knows what Top 10 things
you are ordering by otherwise it will be random.

Then store the highest RowID from this set and store that value in a
variable in the client app -- I will use intRowID for the variable.
Then when the customer wants to look at the next 10 items your sql will
look like this:

Select Top 10 * from tbl1 Where Books Like 'B%' And RowID > intRowID
Order By RowID

You can use a statement like this from the start where intRowID is
initialized to 0, and then takes on the max RowID value for each
respective set of rows the customer selects.

To store the max RowID you can do this:
'--intID would be a global level variable so that it persists -- or you
could store it in a hidden text field in your webpage

Dim intID As Integer
Dim cmd As New ADODB.Command
Dim RS1 As New ADODB.RecordSet, RS2 As New ADODB.Recordset

cmd.Connection = "..."
..

set RS1 = cmd.Execute("Select Top 10 * From tbl1 Where Books Like 'B%'")

set RS2 = cmd.Execute("Select Max(RowID) From (Select Top 10 * From tbl1
Where Books Like 'B%' And RowID > " & intRowID " Order By RowID) t1")

intRowID = RS1(0)

Another option that would be even easier (in the long run) would be to
step up your whole project to ASP.Net.

Rich

Reply to this Message

Rich P - 27 Nov 2007 21:32 GMT

I left out

And RowID > " & intRowID

Dim intID As Integer
Dim cmd As New ADODB.Command
Dim RS1 As New ADODB.RecordSet, RS2 As New ADODB.Recordset

cmd.Connection = "..."
..

set RS1 = cmd.Execute("Select Top 10 * From tbl1 Where Books Like 'B%'
And RowID > " & intRowID)

set RS2 = cmd.Execute("Select Max(RowID) From (Select Top 10 * From tbl1
Where Books Like 'B%' And RowID > " & intRowID " Order By RowID) t1")

intRowID = RS1(0)

Rich

Reply to this Message

eselk@surfbest.net - 27 Nov 2007 21:50 GMT

> It sounds like you have a web based client application.

Actually, it is more of a client/server thin-client. It is a desktop
app on the client side (EXE), not something that runs in a browser.

> If a customer wants to look at a list of books that start with the
> letter "B" you can select the top 10 rows from a set where Books Like
> 'B%'
>
> Select Top 10 * From tbl1 Where Books Like 'B%' Order By RowID

OK, let's use your example, since this is basicly the same as what I'm
trying to. However, let's say I want them to search by Author, not
Title, and lets say I want the results to be sorted by Auther, then
Year Published, then Title.

In your example, I think the books would be in random order (RowID
only, which is probably the order they were added to the database)...
or am I missing something?

I know I can add more fields to the ORDER BY part, but then...

> Then store the highest RowID from this set and store that value in a
> variable in the client app -- I will use intRowID for the variable.
[quoted text clipped - 3 lines]
> Select Top 10 * from tbl1 Where Books Like 'B%' And RowID > intRowID
> Order By RowID

How messy would this part be? Instead of "RowID > intRowID", wouldn't
I need something that starts to look like this:

Auther > intAuther OR (Auther = intAuther AND YearPublished >
intYearPublished) OR (Auther = intAuther AND YearPublished =
intYearPublished AND Title > intTitle)

If it was just the above, that isn't too bad, but I think I actually
need a lot more combinations (more AND/ORs) to really cover every
possible combination of the last/next record having matching Auther,
Year, and/or Title.

If you can ORDER by some unique field, then I think your idea works
great... otherwise I don't see how I could make this work.

HOWEVER -- NOW I'M STARTING TO WONDER ABOUT SOMETHING ELSE. With this
complex of a WHERE clause (the one I started to show above)... Is any
number of indexes even going to help optimize the query so that it
doesn't end up scanning the entire index anyway? If the database
engine is going to end up jumping to B% (assuming it is indexed) then
going one at a time until it gets to > IntRowID, then that really
didn't buy me much over me doing the scan part with ADO and just
having a simple SQL statement.

I know in a true client/server design, your more worried about the
amount of data actually coming back, not as much about how the server
locates the starting record in the index. In my case, the database is
going to be local to my server (which is the middle-man between the
database and clients), so the method the database engine uses to do
the query is important to me, since that amount of time could be >=
the actual reading of the records.

> Another option that would be even easier (in the long run) would be to
> step up your whole project to ASP.Net.

I wish there were some higher level tools I could use, I'm not against
them at all. For example, something from .NET might be easier than
ADO for working with MSSQL, if I used MSSQL. However, it doesn't seem
like it buys me much unless I use .NET on the cient side to connect
directly to my MSSQL server. So now my thin-client isn't as thin,
because it needs .NET. ALSO, and this is the real killer, I need a
costly license to use MSSQL server this way (I need that $25,000+/-?
license to allow the general public to connect). If I route requests
through my own winsock server, and my winsock server maintains only a
few *local* connections to MSSQL (or currently I'm using Access/Jet)
then I don't think I need that costly license. Also, my thin-client
is really thin, all it needs is winsock to talk with my server. I can
also change out the backend database without updating the client side
(for example, if I find out that I am violating some M$ licenses by
doing things the way I am).

I wish Microsoft had more options for client/server database apps, but
I guess they already feel they have the best option, MSSQL, but of
course that doesn't work for people not willing to pay for it. Don't
get me wrong, I'm not all about not paying for stuff, but MS gives us
so many nice APIs for basic desktop apps FOR FREE, just wish the
client/server stuff could be the same -- with lots of options, and
part of the CORE/system API, not extras that may not be installed or
may be different versions on each PC.

Reply to this Message

Rich P - 27 Nov 2007 22:18 GMT

One other trick would be to use TempDB, but that requires a server based
database.

First you select your Top 10 * where Author like ? and book like ? and
yearPublished = ? Order By RowID

This set will contain only the records that meet your criteria but still
get ordered by RowID

Then you store that result in a #tmp table in TempDB

Create Table #tmp1(Author varchar(50), Title varchar(50)... RowID int)

Now you can order this set any way you like and you can still pick out
the max RowID. So for the next selection you can start from max(RowID)
+ 1

As you can see, there are plenty of options for your web app and plenty
of tools. Access just doesn't happen to be the tool of choice for these
kinds of operations.

Java is also quite popular because it is not proprietary like .Net. But
for EASE Of USE for more sophisticated operations like yours -- as long
as you are using Microsoft technology, nothing can beat .Net with sql
Server (specifically VB.Net -- ASP.Net with VB.Net). I could go on, but
that would be a topic for a .Net forum.

Rich

Reply to this Message

eselk@surfbest.net - 27 Nov 2007 23:14 GMT

> One other trick would be to use TempDB, but that requires a server based
> database.
[quoted text clipped - 24 lines]
>
> Rich

You know a lot about MSSQL and .NET. What newsgroup would be better
if I want to explore those options? For example, I'd love to know
what someone using MSSQL would do in my case. I'd prefer not to just
have a real simple .NET solution though, but would like to know how
they would do it at the SQL level (or if it was a .NET solution, at
least know what SQL .NET is using behind the scenes).

From a higher level (GUI level) here is what I want to do... I already
know how to do it using Access Forms/Controls, and I didn't want a
simple answer involving Access Forms which is why I was kind of hiding
the higher level stuff so far.

I've got a list of contacts, could be 100,000 records in the list.
Each record has last name, first name, company, and phone number
(these are the fields I want to display). I want the list to be
sorted by last name, first name, company, then phone number (and there
really could be items that have the same last name, first name, and
company, and only the phone number is different). As the user types
in an edit field, I want to display only the items where the last name
starts with the letters they've typed. If they hit "a" I want to
start showing names right away, which means there could be 10,000 or
more records that start with A, and bringing all of those over from
the server isn't an option. I want to bring over 10, the 10 I display
on the screen, but I also want to give them an option of scrolling
down and seeing the next 10. I've already got code working to bring
up the first 10, and it is fast enough (over a 100mbps LAN) that you
don't notice any delay while typing, so I know "in theory" it is a
workable design.

I know you've mentioned 2 ideas already, but I can't imagine people
using either of those ideas in this case, but it does seem like
something you see in enough programs that it should be pretty common.
Your first idea would not work because I need to sort by fields other
than RowID. Your 2nd idea gets by that problem, but since I may have
100s of users using this look-up feature at the same time (and each
would have a different set of contacts) I can't imagine that creating
100s of temporary tables is really an option (unless they are
optimized to some very extreem point that I don't know about).

Maybe someone will say client/server database isn't good for this type
of GUI. However, I've got this type of GUI working with an ISAM
database where the files are stored on a network drive, and if that
can work, then client/server should work AT LEAST that well since
technicly I'm already *dumb* client/server if you consider the network
redirector the database server (the file server). I'm already not
taking advantage of any local cache, because everything is multi-user
friendly and all caching is disabled.

Anyway, since I'm probably talking about MSSQL now (although I think I
may be able to adapt to Access if I figure out the MSSQL approach),
maybe I need to move to another newsgroup.

Reply to this Message

Salad - 27 Nov 2007 21:09 GMT

There probably is a better way but what the heck. I'm using DAO, not ADO.

I created a function called GetID(). It basically opens a table, moves
to the "next 10" first record. It then gets the ids for the 10 records
(primary key...in my case an autonumber) of this group and returns to
the sub Get10s() the 10 id numbers.

Get10s() asks if you want to get the next 10 records. If yes, it calls
GetID(), gets the next 10 ids and then SQL Selects the IDs that are in
the GetIDs() set. It then displays the ids and asks if you want the
next 10.

I have found that if you have a SQL statement like
    Select Table1.FieldList from Table1 Where ID IN (1,2,3)
it returns a set of records quite nimbly. This would be much faster
than a statement that creates a counter like
    Select Table1.FieldList,
        Dcount("ID","Table1","ID <= [ID]) As RowNumber
        From Table1

Anyway, here's my sample routines....with minimal error checking

Function GetID(lngNum As Long) As String
Dim rst As Recordset
Dim strID As String
Dim intCnt As Integer

Set rst = CurrentDb.OpenRecordset("Table1", dbOpenSnapshot)
rst.MoveFirst
rst.Move lngNum - 1
Do While True
intCnt = intCnt + 1
If intCnt > 10 Or rst.EOF Then Exit Do
strID = strID & rst!ID & ","
rst.MoveNext
Loop

'remove trailing comma at end of strID
GetID = Left(strID, Len(strID) - 1)

End Function

Sub Get10s()
Dim lngNum As Long
Dim strIDs As String
Dim strSQL As String
Dim strMsg As String
Dim rst As Recordset

lngNum = 1

Do While True
If MsgBox("Get Next 10?", vbYesNo + vbQuestion, _
    "Get Recs") = vbYes Then

    'get the record ids of records to display
strIDs = GetID(lngNum)

    'prepare to go to next set of 10 records
lngNum = lngNum + 10

strSQL = "Select Table1.* From Table1 " & _
        "Where ID In (" & strIDs & ")"

    'create the list of those record keys to display
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
rst.MoveFirst
strIDs = ""
Do While Not rst.EOF
strIDs = strIDs & rst!ID & vbNewLine
rst.MoveNext
Loop

    'display your 10 record ids
MsgBox strIDs
Else
Exit Do
End If
Loop
MsgBox "Done"
End Sub

Reply to this Message

eselk@surfbest.net - 27 Nov 2007 22:17 GMT

> es...@surfbest.net wrote:
> > I need to do a query and limit it to returning 10 rows at a time. I
[quoted text clipped - 7 lines]
> (primary key...in my case an autonumber) of this group and returns to
> the sub Get10s() the 10 id numbers.

> Anyway, here's my sample routines....with minimal error checking
>
[quoted text clipped - 6 lines]
> rst.MoveFirst
> rst.Move lngNum - 1

I think those 3 lines above would be too slow in my case. Lets assume
"Table1" has 100,000 records, and you need the last 10. Either the
call to OpenRecordset, or that call to Move, is going to be really
slow. I'm not sure which one, since your opening as a snapshot I
think it would be the call to OpenRecordset since it makes a snapshot
of the entire table to work with. If you switched to a dynamic
(keyset?) recordset, then I think it would be the call to Move that
would be slow because it would take a long time to locate that record
number. Is that not correct? Or maybe it only takes a long time if
your recordset is sorted? In my case my recordset has an ORDER BY
with 4 fields. I can index all 4 if I need to, but even then I don't
think it would help, I still think it will take a long time to locate
(Move to) record 99,990. Since I already know what record 99,990
minus 1 is, there should be a way I can use that to my advantage (but
hopefully without a super long SQL statement).

Reply to this Message

Salad - 27 Nov 2007 23:29 GMT

>>es...@surfbest.net wrote:
>>
[quoted text clipped - 24 lines]
> call to OpenRecordset, or that call to Move, is going to be really
> slow.

Why?

I know you could open up the recordset in Get10s() instead of GetID()
and pass the recordset to GetID().

I'm not sure which one, since your opening as a snapshot I

> think it would be the call to OpenRecordset since it makes a snapshot
> of the entire table to work with. If you switched to a dynamic
> (keyset?) recordset, then I think it would be the call to Move that
> would be slow because it would take a long time to locate that record
> number. Is that not correct?

If Microsoft or whoever is the maker of the database can't get to a row
quickly, then yes. I should think it'd almost be like opening a text
file and moving the file pointer to the xth byte.

Or maybe it only takes a long time if

> your recordset is sorted? In my case my recordset has an ORDER BY
> with 4 fields.

I'm not sure what sorting has to do with the AbsolutePosition or a record.

I can index all 4 if I need to, but even then I don't

> think it would help, I still think it will take a long time to locate
> (Move to) record 99,990.

I don't know either...in your case. If I filter on those fields I
would. I should think it would be faster if you search on fields if
they were sorted and indexed.

Since I already know what record 99,990

> minus 1 is, there should be a way I can use that to my advantage (but
> hopefully without a super long SQL statement).

I really don't know your problem or situation. I figure if I tell
Microsoft or whoever that I want to move to record xyz, it'll take me
there as quickly as possible. I was just showing a method of returning
a specific set of records. Like I said initially, there's probably
better ways.

Reply to this Message

eselk@surfbest.net - 28 Nov 2007 15:13 GMT

> I really don't know your problem or situation. I figure if I tell
> Microsoft or whoever that I want to move to record xyz, it'll take me
> there as quickly as possible. I was just showing a method of returning
> a specific set of records. Like I said initially, there's probably
> better ways.

...and incase I forgot, or didn't sound grateful for your help, Thank
You!

-Eric

Reply to this Message