SQL query ORDER BY gives different result every once in a while (5 second interval)



  •     SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts 
    INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) 
    INNER JOIN wp_product dg ON dg.post_id = wp_posts.ID 
    AND ( dg.location IN ('XV', 'QV', 'DH') ) 
    AND (0 OR (dg.srp = 1) OR (dg.SoldDate > (now() - interval 30 DAY) 
    AND dg.isPremium = 0 AND dg.SoldDate != '0000-00-00 00:00:00'
    AND dg.SoldDate IS NOT NULL AND price > 0 AND NoImage = 0)) 
    AND ( dg.deleted IS NULL OR dg.deleted <> 1 ) 
    WHERE 1=1 AND ( wp_postmeta.meta_key = '_product_info_new' ) 
    AND wp_posts.post_type = 'product' 
    AND (wp_posts.post_status = 'publish') 
    GROUP BY wp_posts.ID 
    ORDER BY dg.SoldDate IS NULL, dg.SoldDate ASC, dg.isPremium DESC LIMIT 0, 30
    

    This query gives me a different result every time on my local development environment even though the database doesn't change at all. The same number of product. I checked the count of the main table it queries and the number doesn't change. So I am wondering if there's a way to stop it from randomizing the results every once and then. Also, I don't see anything that can make the result randomized.



  • Add , wp_posts.ID to the end of the ORDER BY to make it deterministic.

    And follow Akina's advice.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2