Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 15 of 15
  1. #1
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Macintosh 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?

  • #2
    Regular Coder
    Join Date
    Aug 2002
    Location
    Texas
    Posts
    287
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #3
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    okay thanks alot!!!!

  • #4
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    hey the Top N thingy didn't work
    I'm gonna find a way to download the internet if its the last thing I do...
    Prepare to bow down to me (or my grave) and call me almighty when the algorithm is finished

  • #5
    Regular Coder
    Join Date
    Oct 2004
    Location
    In front of this computer. (Where else?)
    Posts
    442
    Thanks
    0
    Thanked 0 Times in 0 Posts
    See this link for Oracle.
    You'll find some examples here for MS Sql.
    Tomorrow is the first day of the rest of your life... What have you done today?
    Tutorials
    Web: w3schools, htmldog General tips: Hardware, Networking

  • #6
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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!
    I'm gonna find a way to download the internet if its the last thing I do...
    Prepare to bow down to me (or my grave) and call me almighty when the algorithm is finished

  • #7
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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?
    I'm gonna find a way to download the internet if its the last thing I do...
    Prepare to bow down to me (or my grave) and call me almighty when the algorithm is finished

  • #8
    Regular Coder
    Join Date
    Aug 2002
    Location
    Texas
    Posts
    287
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #9
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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'm gonna find a way to download the internet if its the last thing I do...
    Prepare to bow down to me (or my grave) and call me almighty when the algorithm is finished

  • #10
    Regular Coder
    Join Date
    Aug 2002
    Location
    Texas
    Posts
    287
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #11
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I think I get what u're hinting at.... lets see.

    Consider the statement....
    Code:
    "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....
    I'm gonna find a way to download the internet if its the last thing I do...
    Prepare to bow down to me (or my grave) and call me almighty when the algorithm is finished

  • #12
    Regular Coder
    Join Date
    Aug 2002
    Location
    Texas
    Posts
    287
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #13
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    thanks man!
    I'm gonna find a way to download the internet if its the last thing I do...
    Prepare to bow down to me (or my grave) and call me almighty when the algorithm is finished

  • #14
    Regular Coder
    Join Date
    Jul 2004
    Location
    France
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #15
    Regular Coder
    Join Date
    Dec 2004
    Location
    Jamaica
    Posts
    592
    Thanks
    2
    Thanked 0 Times in 0 Posts
    thats true....
    thanks guys!
    I'm gonna find a way to download the internet if its the last thing I do...
    Prepare to bow down to me (or my grave) and call me almighty when the algorithm is finished


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •