SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Why ORDER BY is being lost in case of UNION

    Hi,

    We have 2 SELECT that are being joined via UNION.
    The code being like this:

    (SELECT x, y, z
    FROM swd, swv
    WHERE swv .keyword = '$find'
    AND likes > 0
    AND swv .meta_id = swd.id
    AND display = 'yes'
    ORDER BY likes DESC)
    UNION
    (SELECT x, y, z
    FROM swd
    WHERE MATCH (kw1, kw2, kw3, kw4, kw5, kw6, kw7, kw8, kw9, kw10) AGAINST('$find')
    AND display = 'yes'
    LIMIT $start, $page)

    So, when the 1st SELECT is run by itself it does correctly return the results ORDERed BY number of likes DECS

    However, when the 2 SELECTS are run together in the UNION, the ORDER BY DESC of the 1st SELECT is lost!
    What is going on?
    What do we need to do to maintain ORDER BY DESC when the 2 SELECTS are in UNION?

    ThanX
    Dean

    Anoox search engine volunteer

    www.anoox.com

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,263
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    UNIONS cause orders to be lost as it does a match up. You can fake it by adding a bogus limit to the union, then order the whole result.

    Code:
    SELECT x
    	 , y
    	 , z
      FROM (SELECT x
    			 , y
    			 , z
    			 , limit
    		  FROM swd
    		  JOIN swv ON swv.meta_id = swd.id AND swv.keyword = '$find'
    		 WHERE likes > 0
    		   AND display = 'yes'
    		 UNION
    		SELECT x
    			 , y
    			 , z
    			 , -1 AS limit
    		  FROM swd
    		 WHERE MATCH (kw1, kw2, kw3, kw4, kw5, kw6, kw7, kw8, kw9, kw10) AGAINST('$find') 
    		   AND display = 'yes' 
    		 LIMIT $start, $page) sq
      ORDER BY limit DESC
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WorldNews View Post
    What is going on?
    simple -- an ORDER BY is valid only on the results of the union, not on the individual subqueries

    did you know that when you specify UNION without the ALL keyword, the database has to take the entire combined result set and sort it into sequence by all the columns in the SELECT clauses?

    it does this so that it can detect and remove duplicate result rows

    so then if you have an ORDER BY clause, the entire result set, minus duplicates, is then sorted all over again
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    So what do you suggest?
    That we do not use UNION and instead 1st we generate the list returned by the 1st SELECT and then the list by the 2nd SELECT?


    Quote Originally Posted by r937 View Post
    simple -- an ORDER BY is valid only on the results of the union, not on the individual subqueries

    did you know that when you specify UNION without the ALL keyword, the database has to take the entire combined result set and sort it into sequence by all the columns in the SELECT clauses?

    it does this so that it can detect and remove duplicate result rows

    so then if you have an ORDER BY clause, the entire result set, minus duplicates, is then sorted all over again

    Anoox search engine volunteer

    www.anoox.com

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WorldNews View Post
    So what do you suggest?
    depends on what you are trying to achieve and why

    did you try the solution in post #2?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    DaveMaxwell ,

    I cannot make sense of your MySQL code as applied to my fake code to take it to the real code.
    So here is the real code, can you please give your suggestion to correct this lost ORDER BY problem to the real code, here:

    (SELECT swd.id, ix_id, title, descpt, category
    FROM swd, swv
    WHERE swv.keyword = '$find'
    AND swv.vote_up > 0
    AND swv.meta_id = swd.id
    AND swd.display = 'yes'
    ORDER BY swv.vote_up DESC)
    UNION
    (SELECT id, ix_id, title, descpt, category
    FROM swd
    WHERE MATCH (title, descpt, kw1, kw2, kw3, kw4, kw5, kw6, kw7, kw8, kw9, kw10) AGAINST('$find')
    AND display = 'yes'
    LIMIT $start, $page)


    Thanks.
    Dean

    Anoox search engine volunteer

    www.anoox.com

  7. #7
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,263
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by WorldNews View Post
    DaveMaxwell ,

    I cannot make sense of your MySQL code as applied to my fake code to take it to the real code.
    So here is the real code, can you please give your suggestion to correct this lost ORDER BY problem to the real code, here:
    All I did was reformat your query to use the join syntax - which the dbms will do anyways, so my way is slightly more efficient. But essentially what I did was make your query a sub-query (except for the order in the first query because it's redundant due to the UNION) I take the result set from the UNION and sort it.

    Code:
    SELECT swd.id
         , ix_id
    	 , title
    	 , descpt
    	 , category 		
      FROM (SELECT swd.id
    			 , ix_id
    			 , title
    			 , descpt
    			 , category
    			 , swv.vote_up
    		  FROM swd
    		 INNER JOIN swv ON swv.meta_id = swd.id 
    					   AND swv.vote > 0
    		 WHERE swv.keyword = '$find' 
    		   AND swd.display = 'yes' 
    		 UNION
    		SELECT id
    			 , ix_id
    			 , title
    			 , descpt
    			 , category 
    			 , -1 AS vote_up
    		  FROM swd 
    		 WHERE MATCH (title, descpt, kw1, kw2, kw3, kw4, kw5, kw6, kw7, kw8, kw9, kw10) AGAINST('$find') 
    		   AND display = 'yes' 
    		 LIMIT $start
    			 , $page) SQ
    ORDER BY swv.vote_up DESC
    Or if you want to see it without optimizing yours
    Code:
    SELECT swd.id, ix_id, title, descpt, category 		
      FROM ((SELECT swd.id, ix_id, title, descpt, category 
    		   FROM swd, swv 
    		  WHERE swv.keyword = '$find' 
    		    AND swv.vote_up > 0 
    		    AND swv.meta_id = swd.id 
    		    AND swd.display = 'yes' 
    		  ORDER BY swv.vote_up DESC) 
    		UNION
    		(SELECT id, ix_id, title, descpt, category 
    		   FROM swd 
    		  WHERE MATCH (title, descpt, kw1, kw2, kw3, kw4, kw5, kw6, kw7, kw8, kw9, kw10) AGAINST('$find') 
    		    AND display = 'yes' 
    		  LIMIT $start, $page) SQ
    ORDER BY swv.vote_up DESC
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WorldNews View Post
    I cannot make sense of your MySQL code as applied to my fake code to take it to the real code.
    doesn't this say something about the wisdom of posting fake code

    and no, that wasn't a question
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    These suggestions do not work, as I suspected since the
    swv.vote_up
    only exists in the 1st Table. That is why we were doing the UNION to start with since one Table is based on vote_up
    but not the other Table.

    FYI, MySQL gives Error message:

    Table 'swv' from one of the SELECTs cannot be used in global ORDER clause


    Quote Originally Posted by DaveMaxwell View Post
    All I did was reformat your query to use the join syntax - which the dbms will do anyways, so my way is slightly more efficient. But essentially what I did was make your query a sub-query (except for the order in the first query because it's redundant due to the UNION) I take the result set from the UNION and sort it.

    Code:
    SELECT swd.id
         , ix_id
    	 , title
    	 , descpt
    	 , category 		
      FROM (SELECT swd.id
    			 , ix_id
    			 , title
    			 , descpt
    			 , category
    			 , swv.vote_up
    		  FROM swd
    		 INNER JOIN swv ON swv.meta_id = swd.id 
    					   AND swv.vote > 0
    		 WHERE swv.keyword = '$find' 
    		   AND swd.display = 'yes' 
    		 UNION
    		SELECT id
    			 , ix_id
    			 , title
    			 , descpt
    			 , category 
    			 , -1 AS vote_up
    		  FROM swd 
    		 WHERE MATCH (title, descpt, kw1, kw2, kw3, kw4, kw5, kw6, kw7, kw8, kw9, kw10) AGAINST('$find') 
    		   AND display = 'yes' 
    		 LIMIT $start
    			 , $page) SQ
    ORDER BY swv.vote_up DESC
    Or if you want to see it without optimizing yours
    Code:
    SELECT swd.id, ix_id, title, descpt, category 		
      FROM ((SELECT swd.id, ix_id, title, descpt, category 
    		   FROM swd, swv 
    		  WHERE swv.keyword = '$find' 
    		    AND swv.vote_up > 0 
    		    AND swv.meta_id = swd.id 
    		    AND swd.display = 'yes' 
    		  ORDER BY swv.vote_up DESC) 
    		UNION
    		(SELECT id, ix_id, title, descpt, category 
    		   FROM swd 
    		  WHERE MATCH (title, descpt, kw1, kw2, kw3, kw4, kw5, kw6, kw7, kw8, kw9, kw10) AGAINST('$find') 
    		    AND display = 'yes' 
    		  LIMIT $start, $page) SQ
    ORDER BY swv.vote_up DESC

    Anoox search engine volunteer

    www.anoox.com

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    okay, i will help you fix this problem, if you would kindly test and then comment on each of the following two queries

    first query --
    Code:
    SELECT swd.id
         , swd.ix_id
         , swd.title
         , swd.descpt
         , swdcategory 
      FROM swd
    INNER
      JOIN swv 
        ON swv.meta_id = swd.id 
       AND swv.keyword = '$find' 
       AND swv.vote_up > 0 
     WHERE swd.display = 'yes'
    second query --
    Code:
    SELECT id
         , ix_id
         , title
         , descpt
         , category 
      FROM swd 
     WHERE MATCH(title, descpt, kw1, kw2, kw3, kw4, kw5, kw6, kw7, kw8, kw9, kw10) 
           AGAINST('$find') 
       AND display = 'yes'
    do these two queries separately produce the correct results? please also explain why the second query appears to have paging ($start,$page) but the first one doesn't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Wizard WorldNews's Avatar
    Join Date
    Nov 2007
    Posts
    1,033
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    No. The 1st SELECT is way off the Mark. It will not work at all since the vote_up is coming from the 2nd Table of swv.
    I see that my question is too difficult to answer via this forum. And by the time I explain the complexities of the problem that will become a problem on to its own.
    So I have to work out the problem myself.
    Thank anyway.

    Anoox search engine volunteer

    www.anoox.com

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by WorldNews View Post
    No. The 1st SELECT is way off the Mark.
    a bit of an exaggeration, i think

    Quote Originally Posted by WorldNews View Post
    It will not work at all since the vote_up is coming from the 2nd Table of swv.
    and what, may i ask, is wrong with this --
    Code:
    swv.vote_up > 0
    seems like that's exactly what you said it should be

    Quote Originally Posted by WorldNews View Post
    I see that my question is too difficult to answer via this forum.
    duh...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •