SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Zealot akohl's Avatar
    Join Date
    Jun 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    retrieving certain number of records with sql

    I want to present a certain number of records from a database and enable the user to click a link to get "next 10 records" as a means of scrolling through the entire table.

    Can I use sql to retrieve a certain number of records in a select? I thought that maybe there was some way to use the absolutePosition property in the WHERE clause. Is there such a thing in SQL? As far as I know, one can only use values of fields in the WHERE clause.

    I know that I can use vb script on the asp page to count the records as I display them and control how many I display at once. My question is if there is a way to do this more efficiently with sql.

    I'm using an ms access

    Thanks in advance

  2. #2
    Web developer chrisranjana's Avatar
    Join Date
    Jan 2001
    Location
    chennai , tamil nadu , India
    Posts
    710
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ms access limit statement

    did you mean

    select * from tablename where some condition LIMIT 0 , 10

    etc
    Chris, Programmer/Developer,
    Laravel Php Developers, Ruby on Rails programmers,
    Moodle, Opencart, Magento, Geodesic Classifieds/Auctions,
    www.chrisranjana.com

  3. #3
    SitePoint Zealot akohl's Avatar
    Join Date
    Jun 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I don't think this works in access.
    I tried it like this and got a syntax error.
    Am I doing something wrong here.

    Code:
    SELECT [books].[book_id], [books].[title] 
    FROM books
    LIMIT 0,10;
    I'm haveing some trouble with my office help files so I can't even look up the entry on the keyword you suggested using.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    on the first call to the database --
    Code:
    select top n
           id, foo, bar
      from yourtable
     where whatever
    order 
        by sortfield
    since this is the first group, when your asp page processes these n rows, your "prev" link will be greyed out, and there will be a link to "next n"

    this link will carry the primary key of the nth row (the last one displayed)

    when the user clicks the link, the action page takes the value of the nth id and runs this query --
    Code:
    select top n
           id, foo, bar
      from yourtable
     where whatever
       and id > nth id
    order 
        by sortfield
    get the idea?


    rudy

  5. #5
    SitePoint Zealot akohl's Avatar
    Join Date
    Jun 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    got it!

    Thanks. Your suggestion will work for me, sort of. But I don't really want to present the data in order that it was entered which is what I'd be doing if I relied on the auto-increment numerical primary key. So I was hoping that there was some property like absolutePosition of the ado recordset object that was accessible to sql. I guess it doesn't really exist.

    Now for a theoretical question. Is this limitation true of sql in general or just access?

    Judging by chrisranjana's answer there is away to do this with sql. But it doesn't work in Access.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i think you misunderstand which records are presented when you use SELECT TOP N ID, FOO, BAR

    the rows are not sorted by ID at all, but rather by whatever you put in the ORDER BY clause

    ORDER BY works in all databases

    it's TOP that only works in access (and sql server)

    it's a common misconception, similar to the way that people think the DISTINCT keyword applies to the column immediately following it, as in SELECT DISTINCT FOO, BAR, ...

    DISTINCT actually applies to all selected columns, i.e. rows

    similarly, TOP applies to rows


    rudy

  7. #7
    SitePoint Zealot akohl's Avatar
    Join Date
    Jun 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Lets say instead of ORDER BY itemId which is an auto-increment numberical field, I use ORDER BY title, which is a string value field, when I want to get my second 10 lines I wont be able to filter out the first 10 lines with a mathematical expression in the WHERE clause, will I?

  8. #8
    SitePoint Enthusiast
    Join Date
    Jan 2003
    Location
    San Diego
    Posts
    49
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First off, let's stay away from DB-specific code

    LIMIT 0, 10 is mySQL, not generic SQL.

    The easiest and most compatable (however not as scalable, but you are using access, so you obviously aren't concerned with scalability) is to take a querystring variable that increments +1 on next -1 on prev. Then multiply that by the page size (10 lines), and skip that many lines ahead using your ASP, PHP, etc. If you are using Microsoft ADO (which you probably should), you can use the nextrow method. ADO also has a method that is available with certain cursors that lets you skip to a specific row.
    Kyle Maxwell
    www.kylemaxwell.com

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)

    oh, please

    "skip that many lines ahead"

    huh?

    kyle, are you suggesting that he run the same query (all rows) no matter which rows he wants to display?

    ewww!

    as for staying away from database-specific sql, i disagree strongly

    please show me your non-db-specific "top N" query


    rudy

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    akohl, sorry for my earlier query, it was wrong (that happens sometimes when i write sql without testing it)

    here's your strategy when ordering by title

    the first time you run it, your sql is:
    Code:
    select top 10 itemid, title
      from yourtable
     where whatever
    order by title
    this will return your first 10 titles, for example --

    (row) itemid title
    (1) 325 'ABC's of Quattro Pro for Windows, The'
    (2) 5312 'Abel's Theorem and the Allied Theory'
    (3) 34 'Aborigines of Western Australia'
    (4) 18211 'Abridged History of the United States, An'
    (5) 635 'Abstract of Systematic Theology'
    (6) 6434 'Adonais: An Elegy on the Death of John Keats'
    (7) 1013 'Advanced Color Imaging on the Mac OS'
    (8) 937 'Adventures of Huckleberry Finn, The'
    (9) 83472 'Aesop's Fables by Aesop'
    (10) 27 'After Death -- What?'

    when the user clicks the "next" link, you pass itemid=27 to the action page which then runs the following query:
    Code:
    select top 10 itemid, title
      from yourtable
     where whatever
       and title > 
           ( select title from yourtable
              where itemid = 27 )
    order by title
    does that make more sense?

    rudy

  11. #11
    SitePoint Zealot akohl's Avatar
    Join Date
    Jun 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yeah. That looks good. Thanks. I guess I forgot that you can use the > operator which comparing strings.

    By the way regarding KarlMaxwells suggestion, is there any limit to how much data can be stored by the recordset object? How about when I use the getrows method and dump data into an array. What's the limit on how much data I can work with that way and what happens when I approach or go over that limit?

  12. #12
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And if I have page size = 10 records and want to jump from page 1 (records 1-10) to page 3 (records 21-30)?

    This is a way to do it with Sql server: http://www.fawcette.com/dotnetmag/20...ting/page2.asp
    I guess it doesn't help akohl, since you can't define table variables in Access

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    from the page you cited, jofa:
    This means that as users request higher and higher pages, the performance for each page will slow down a bit. For instance, to create page 100 (rows 2451 through 2500) you'd have to fill the table variable or temp table with 2,500 rows before selecting out those with a rownum greater then or equal to 2451.
    any scheme that allows you to jump around in a result set -- and note, google does -- has to have a query that is a bit more sophisticated than the query for a simple "next 10 records" link, which was the original requirement

    rudy

  14. #14
    SitePoint Member
    Join Date
    Jan 2005
    Location
    spore
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what if you got a previous link? How do you going to keep track of previous link?

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    wow, resurrecting a two-year-old thread!!

    pinggy, could you please rephrase your question?

    perhaps show the query that you are working on, too

    because there are so many "paging" solutions, it is important to know which strategy you are using
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Member
    Join Date
    Jan 2005
    Location
    spore
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh..
    Currently, Im researching on how to do database paging similar to yahoo so I happened to read on this issue which is similar to what I might need.
    Im using ms access.

    So is there any guidance that you can help?

  17. #17
    SitePoint Addict danfran's Avatar
    Join Date
    Jan 2005
    Location
    New York City
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why don't you check out the ASP forum and see the recent posts on paging using ASP. This is how it's done. ADO has built-in functionality explicitly for this purpose!!! Don't reinvent the wheel..

    Dan


Bookmarks

Posting Permissions

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