Retrieve SOME records

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:

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.

fv

okay thanks alot!!!!

hey the Top N thingy didn't work :confused:

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.

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:

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?

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?

fv

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?

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.

fv

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....

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


fv

:thumbsup: thanks man!

when you click next page you just send the lastnumber (last ID) in the queryString

then

"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

thats true....
thanks guys!