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"
Db2 uses "FETCH FIRST N ROWS ONLY"
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....
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' :
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