SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2006
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    LIMIT posts conditionaly

    I have a table with two columns in one table one is posted(date) and other hidden(boolean). I want to select all posts that are not hidden with limit and offset plus all other posts that are potest on a same date with the one selected from the not hidden posts.

    I've tried something like this:

    Code:
    (   
        select * FROM item WHERE hidden = 0 LIMIT 20 OFFSET 0
    )
    UNION
    (
    
        select * FROM item as i1 WHERE i1.hidden = 1 AND i1.posted in (select i2.posted FROM item as i2 WHERE i2.hidden = 0 LIMIT 20 OFFSET 0)
    
    ) ORDER BY posted DESC
    And i get this error:
    This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

    Althorugh even if it would work this query makes 3 selects is there a way to optimize and get the same result? With less queries?
    NOTELAY.COM - Have a nice noty day
    ETA CONSULTING

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    since each of the two parts of your UNION returns only 20 rows, just run them separately, and combine/sort the results with your application language (php or whatever)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2006
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well the second one doesn`t return 20 it has limit of 20 in a subquery which actually is not allowed in mysql
    NOTELAY.COM - Have a nice noty day
    ETA CONSULTING

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    yes, i understand, but you can run the subquery, grab the 20 posted values, and use them in an IN list for the outer query

    you gots to do it in steps if you can't upgrade your server
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Sep 2006
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe using ajax to load the hidden when they are needed will be less stressful for the server. Thank you for your time i really appreciate your answers.
    NOTELAY.COM - Have a nice noty day
    ETA CONSULTING


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
  •