Thread: SQL Query help

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    12
    Rep Power
    0

    SQL Query help


    I am trying to convert a MySQL Query to MSSQL. The MySQL query looked like this:

    SELECT * FROM TABLE LIMIT 10,25

    This query selects the first 10 rows starting with row #30. How is it possible to do this in MSSQL? THe purpose behind it is for a multiple page search result....say you want 10 results per page.

    Thanks for the help!
    Matthew
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    > This query selects the first 10 rows starting with row #30
    no, it doesn't, it starts at record 11 and selects 25 records from there (in mysql the offset of the initial row is 0, not 1)

    to do paging properly, you need either to do it in your scripting language (php, asp, coldfusion...) or else if you try to do it in the sql language, specifically in microsoft sql server, you would ordinarily use the TOP keyword, an ORDER BY clause, and acolumn value parameter

    e.g.

    select TOP 10 id, name, address
    from people
    where name > 'jones'
    order by name

    the column value parameter ('jones' in this example) is what is passed in from the scripting language as the "next" link from the page that showed the last set of names

    make sense?

    rudy
    http://r937.com/
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    12
    Rep Power
    0
    Sorry...I posted what it returned incorrectly.

    What I was trying to get at...the TOP command does not include a "start at row" parameter. It allows you to only return the top x rows.

    I am still a little confused on how to return the top x rows starting at row y.

    Thanks!
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    select top x+y-1 foo, bar
    from yourtable
    where foo not in
    ( select top y-1 foo
    from yourtable
    order by foo )
    order by foo

    substitute your numbers for x and y and your column names for foo and bar

    foo is the column that determines the sequence that TOP will operate on

    rudy


    order by foo desc
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    12
    Rep Power
    0
    I got the general idea now. I just think I have the code written wrong here now. What do you think of this?

    $x = $start + $display_number;
    $query3 = "SELECT TOP $x * FROM photos WHERE category = '$category' AND id NOT IN (SELECT TOP $start id FROM photos WHERE category = '$category' ORDER BY id) ORDER BY id DESC";


    It works just on the first page....pages after that contain all the data from the first page plus the next page. So page 10 contains all data from pages 1-10.


    I visited your site...and may be contacting you soon about some projects I have coming up.

    Thanks for the help so far!

    Matthew
    Last edited by MKSolutions; September 20th, 2003 at 12:04 AM.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    well, like i said in my first reply, i wouldn't do it with the double-top (it's not all that efficient)

    on the first page, you just get

    SELECT TOP $start * FROM photos WHERE category = '$category' ORDER BY id

    as you prepare the page, you use the id of the last photo, and use that in the link for the next page

    <a href="listphotos.php?howmany=$display_number&amp;startat=$lastid">next $display_number</a>

    then the incoming logic pulls $lastid out of the url string, and uses it in the query

    SELECT TOP $start * FROM photos WHERE category = '$category'
    AND id > $lastid ORDER BY id

    rudy
    Last edited by r937; September 20th, 2003 at 06:21 AM.

IMN logo majestic logo threadwatch logo seochat tools logo