View Full Version : Retrieve SOME records

01-26-2005, 10:59 PM
Is there a possible way that I can use a Select statement to select say the first five records in a database. That is without having to select more than i have to then use a for loop to scroll through to five?

Any takers? I'm sure this is a really simple question? :thumbsup:

01-26-2005, 11:22 PM
Depends upon the database.

SQL Server uses some verbage like "Top N"

MySQL uses "LIMIT N"


don't know the syntax for any others, but they probably have something similar.


01-26-2005, 11:24 PM
okay thanks alot!!!!

01-30-2005, 07:27 AM
hey the Top N thingy didn't work :confused:

01-31-2005, 09:27 AM
See this link (http://www.techonthenet.com/oracle/questions/top_records.htm) for Oracle.
You'll find some examples here (http://www.databasejournal.com/features/mssql/article.php/3111031) for MS Sql.

01-31-2005, 01:13 PM
Thanks alot. I forgot to repost that I got it tho.

I just didn't ko that the "Top" keyword was to follow the SELECT keyword.... I thought it was to go at the end...

But I saw some stuff about SELECT DISTINCT, DISTINCT ROWS, ALLand so on.....

Thanks alot tho! :thumbsup:

02-03-2005, 01:51 AM
How would i select say record #40 to record #60? Lets say i have a page that shows the first 1-40 records.... right.

Easily done, "Select Top 40"

But now.... i need to click next....
And i'm thinkin there must be a more efficient that grabbin data i don't need on page 2 (1-40).

I don't think the "Between" command will do me much good coz its not like i can just know the 60th record just like that....

Any takers?

02-03-2005, 03:13 AM
Given that you may have N records.

Now - does this next link point back to the same page to be reloaded with the next, say 40 records? If so, you could add a querystring variable of the next ID after the 40 you just returned. First time through it might be 0. Thereafter it could be the ID of the 40TH +1 ID value you just retrieved.
In addition to using the TOP clause to fetch only X number of records, you can further constrain the sql by adding a:
where ID >= the querystring value you recieved either initially (0?) or the value from the previous querystring value of the "next" link.

Kinda make sense?


02-03-2005, 05:40 AM
Yea it makes sense... logical and all.... but uh... the primary key fields are random generators u get me? So the whole criteria thing gets knocked right there.

So I mean i've got to find another way.... I know of the between command but is there like an "after" command... so that i could do summin like "SELECT TOP N FROM ..... AFTER blah blah"

Is summin like that even possible?

02-03-2005, 03:09 PM
I think that so long as you are bring back an ordered list of unique keys, it should not matter, if the values support a concept of "next"....whether it be integers or whatever.


02-03-2005, 05:17 PM
I think I get what u're hinting at.... lets see.

Consider the statement....

"SELECT messageID, messageDate, messageTitle, messageSender FROM messages.... ORDER BY " & sortField

I would have to select the say "top 40.... where the sortField>some value " right?
Would that work? I hope I am getting this whole thing correctly....

02-03-2005, 06:26 PM
Yes, exactly. When you bring back those 40, retain the value of the max one, pass back to yourself in a querystring or somehow retain, and that becomes
the value the next time around to use as 'some value' :
sortField>some value

The same would apply if you wanted to page backwards 40 at a time, you'd keep the min value of each set of 40 and:
sortField < some value


02-04-2005, 01:07 PM
:thumbsup: thanks man!

02-04-2005, 02:19 PM
when you click next page you just send the lastnumber (last ID) in the queryString


"SELECT TO 10 * FROM myTable WHERE ID >" + Request.QueryString("LastNumber") + " ORDER BY ID DESC"

or ASC depending on what you want

i nearlly allways use it

its a better method than to load 10000 datas when nearlly nobody will turn the second page

02-04-2005, 06:26 PM
thats true....
thanks guys!