Page 1 of 3 123 LastLast
Results 1 to 15 of 32

Thread: Help in "Limit"

  1. #1
    Join Date
    Feb 2006
    Posts
    38

    Unanswered: Help in "Limit"

    we all know
    mysql:
    Code:
    select * from table limit ?1, ?2
    equals

    sqlserver:
    Code:
    SELECT TOP ?2 * 
    FROM table WHERE (IDENTITYCOL NOT IN 
    (SELECT TOP ?1 IDENTITYCOL 
    FROM table order by IDENTITYCOL)) 
    order by IDENTITYCOL
    but the below SQL in mysql,how to convert?I enmesh...........
    Code:
    select pageid,pagename,pageaddr,pageauditflag,pageartauditflag,startplaytime
     from pageinfo where entryid= ?1 and startplaytime= ?2 
    limit ?3, ?4
    thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do it the same way -- pretend ?1 and ?2 don't exist, put ?3 where ?1 is and ?4 where ?2 is in the TOP query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2006
    Posts
    38
    Quote Originally Posted by r937
    do it the same way -- pretend ?1 and ?2 don't exist, put ?3 where ?1 is and ?4 where ?2 is in the TOP query
    you mean if the query is:
    Code:
    select * from AselectPageInfo_view where entryid=1 and startplaytime='2006-03-08'  limit 0,1
    I can convert it to below:
    Code:
    SELECT TOP 1  * FROM (select * from AselectPageInfo_view where 
    entryid=1  and startplaytime='2006-03-08'  ) WHERE (pageid NOT IN (SELECT 
    TOP 0 pageid FROM (select * from AselectPageInfo_view where entryid='1' 
     and startplaytime='2006-03-08'  ) order by pageid)) order by pageid
    right?
    but it can't run!
    what's wrong?
    Please help!thanks!

  4. #4
    Join Date
    Feb 2006
    Posts
    38
    oh,May be this is right!
    Code:
    SELECT TOP 1 * FROM AselectPageInfo_view WHERE 
    (pageid NOT IN (SELECT TOP 0 pageid FROM  
    AselectPageInfo_view where 
    entryid='1' and startplaytime='2006-03-08' order by pageid)) 
    and (entryid='1' and startplaytime='2006-03-08') order by pageid
    Expert,Please check it,is it right?
    Thank you from bottom of my heart!

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SELECT TOP 0 is not going to return anything, is it?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2006
    Posts
    38
    no,it return result( one line )
    expert, the query that i write in #4 is right?

  7. #7
    Join Date
    Feb 2006
    Posts
    38
    i mean
    Code:
    select * from AselectPageInfo_view where entryid=1 and 
    startplaytime='2006-03-08'  limit 0,1
    whether equals
    Code:
    SELECT TOP 1 * FROM AselectPageInfo_view WHERE 
    (pageid NOT IN (SELECT TOP 0 pageid FROM  
    AselectPageInfo_view where 
    entryid='1' and startplaytime='2006-03-08' order by pageid)) 
    and (entryid='1' and startplaytime='2006-03-08') order by pageid

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please tell me how many rows you get for this --

    SELECT TOP 0 pageid FROM
    AselectPageInfo_view where
    entryid='1' and startplaytime='2006-03-08' order by pageid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2006
    Posts
    38
    oh,it's zero

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do you know why?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2006
    Posts
    38
    i change the 0 to 1
    that can get one row
    the 0 is reason?

  12. #12
    Join Date
    Feb 2006
    Posts
    38
    hello,expert,why we need know the result? 1 and 0 we can change to another parameter,
    i just want to know the two query whether equals?

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, they are not the same
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Feb 2006
    Posts
    38
    oh,expert,can you tell me which query can equals
    Code:
    select * from AselectPageInfo_view where entryid=1 and 
    startplaytime='2006-03-08'  limit 0,1
    because the limit can't be used in mssql

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that query just gets 1 row -- and since there's no ORDER BY, it could be any row

    so the equivalent in microsoft sql server is
    Code:
    select top 1 * from AselectPageInfo_view where entryid=1 and 
    startplaytime='2006-03-08'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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