MYSQL Union query order issue

The following UNION  query works but does not order the results correctly. The first part selects a small recordset which needs to bne displayed ahead of the main query which displays all the records ordered Article_Posted

(SELECT Articles.Article_ID, Articles.Article_Title, Articles.Article_Excerpt, Articles.Article_Content, Articles.Article_Posted, Articles.Article_Comments, Articles.Article_Comments_Count, Articles.Article_Images, Categories.Category_ID, Categories.Category_Title, Users.User_ID, Users.User_Name
     FROM (FB_Categories Categories
         INNER JOIN FB_Articles Articles ON Categories.Category_ID = Articles.Article_Category_ID)
         INNER JOIN FB_Users Users ON Users.User_ID = Articles.Article_User_ID
                WHERE Articles.Article_Level <=4 AND Articles.Article_Sticky_Date >= CurDate( ) -1            ORDER BY Articles.Article_Sticky_Date)
UNION (
SELECT Articles2.Article_ID, Articles2.Article_Title, Articles2.Article_Excerpt, Articles2.Article_Content, Articles2.Article_Posted, Articles2.Article_Comments, Articles2.Article_Comments_Count, Articles2.Article_Images, Categories2.Category_ID, Categories2.Category_Title, Users2.User_ID, Users2.User_Name
       FROM (FB_Categories Categories2
          INNER JOIN FB_Articles Articles2 ON Categories.Category_ID = Articles.Article_Category_ID)
           INNER JOIN FB_Users Users2 ON Users.User_ID = Articles.Article_User_ID
            WHERE Category_Title <> 'Draft' AND Article_Level <= 4
           ORDER BY Articles.Article_Posted DESC)  ;

Both halves of the query work on their own, but as a union the order goes haywire.

Many thanks
Pete
pzh20Asked:
Who is Participating?
 
Ludwig DiehlSystems ArchitectCommented:
lets suppose you have less than 1000000 rows then this will work:


(SELECT
      a.Article_ID,
      a.Article_Title,
      a.Article_Excerpt,
      a.Article_Content,
      a.Article_Posted,
      a.Article_Comments,
      a.Article_Comments_Count,
      a.Article_Images,
      c.Category_ID,
      c.Category_Title,
      u.User_ID,
      u.User_Name,
      0 AS queryOrder
FROM
      FB_Categories c
      INNER JOIN FB_Articles a ON c.Category_ID = a.Article_Category_ID
      INNER JOIN FB_Users u ON u.User_ID = a.Article_User_ID
WHERE
      a.Article_Level <=4 AND
      a.Article_Sticky_Date >= CURDATE() -1        
ORDER BY queryOrder,a.Article_Sticky_Date LIMIT 1000000)
UNION  
(SELECT
      a.Article_ID,
      a.Article_Title,
      a.Article_Excerpt,
      a.Article_Content,
      a.Article_Posted,
      a.Article_Comments,
      a.Article_Comments_Count,
      a.Article_Images,
      c.Category_ID,
      c.Category_Title,
      u.User_ID,
      u.User_Name,
      1 AS queryOrder
FROM
      FB_Categories c
      INNER JOIN FB_Articles a ON c.Category_ID = a.Article_Category_ID
      INNER JOIN FB_Users u ON u.User_ID = a.Article_User_ID
WHERE
      c.Category_Title <> 'Draft' AND
      a.Article_Level <= 4
ORDER BY queryOrder,a.Article_Posted DESC LIMIT 1000000)

There must be a LIMIT clause within each select in order for it to be ordered otherwise it will not work as described in the article
0
 
hieloCommented:
try:
(SELECT Articles.Article_ID, Articles.Article_Title, Articles.Article_Excerpt, Articles.Article_Content, Articles.Article_Posted, Articles.Article_Comments, Articles.Article_Comments_Count, Articles.Article_Images, Categories.Category_ID, Categories.Category_Title, Users.User_ID, Users.User_Name, 2010 as fake
     FROM (FB_Categories Categories
         INNER JOIN FB_Articles Articles ON Categories.Category_ID = Articles.Article_Category_ID)
         INNER JOIN FB_Users Users ON Users.User_ID = Articles.Article_User_ID
                WHERE Articles.Article_Level <=4 AND Articles.Article_Sticky_Date >= CurDate( ) -1            ORDER BY Articles.Article_Sticky_Date
	)
UNION (
SELECT Articles2.Article_ID, Articles2.Article_Title, Articles2.Article_Excerpt, Articles2.Article_Content, Articles2.Article_Posted, Articles2.Article_Comments, Articles2.Article_Comments_Count, Articles2.Article_Images, Categories2.Category_ID, Categories2.Category_Title, Users2.User_ID, Users2.User_Name, 2009 as fake
       FROM (FB_Categories Categories2
          INNER JOIN FB_Articles Articles2 ON Categories.Category_ID = Articles.Article_Category_ID)
           INNER JOIN FB_Users Users2 ON Users.User_ID = Articles.Article_User_ID
            WHERE Category_Title <> 'Draft' AND Article_Level <= 4
           ORDER BY Articles.Article_Posted DESC)  
		 
		 ORDER BY `fake`

Open in new window

0
 
Ludwig DiehlSystems ArchitectCommented:
Try this:

(SELECT
      a.Article_ID,
      a.Article_Title,
      a.Article_Excerpt,
      a.Article_Content,
      a.Article_Posted,
      a.Article_Comments,
      a.Article_Comments_Count,
      a.Article_Images,
      c.Category_ID,
      c.Category_Title,
      u.User_ID,
      u.User_Name,
      0 AS queryOrder
FROM
      FB_Categories c
      INNER JOIN FB_Articles a ON c.Category_ID = a.Article_Category_ID
      INNER JOIN FB_Users u ON u.User_ID = a.Article_User_ID
WHERE
      a.Article_Level <=4 AND
      a.Article_Sticky_Date >= CURDATE() -1            
ORDER BY queryOrder,a.Article_Sticky_Date)
UNION (
SELECT
      a.Article_ID,
      a.Article_Title,
      a.Article_Excerpt,
      a.Article_Content,
      a.Article_Posted,
      a.Article_Comments,
      a.Article_Comments_Count,
      a.Article_Images,
      c.Category_ID,
      c.Category_Title,
      u.User_ID,
      u.User_Name,
      1 AS queryOrder
FROM
      FB_Categories c
      INNER JOIN FB_Articles a ON c.Category_ID = a.Article_Category_ID
      INNER JOIN FB_Users u ON u.User_ID = a.Article_User_ID
WHERE
      c.Category_Title <> 'Draft' AND
      a.Article_Level <= 4
ORDER BY queryOrder,a.Article_Posted DESC);
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
k_murli_krishnaCommented:
Try this:

SELECT DISTINCT Article_ID, Article_Title, Article_Excerpt, Article_Content, Article_Posted, Article_Comments, Article_Comments_Count, Article_Images, Category_ID, Category_Title,User_ID,User_Name FROM(
(SELECT Articles.Article_ID, Articles.Article_Title, Articles.Article_Excerpt, Articles.Article_Content, Articles.Article_Posted, Articles.Article_Comments, Articles.Article_Comments_Count, Articles.Article_Images, Categories.Category_ID, Categories.Category_Title, Users.User_ID, Users.User_Name
     FROM (FB_Categories Categories
         INNER JOIN FB_Articles Articles ON Categories.Category_ID = Articles.Article_Category_ID)
         INNER JOIN FB_Users Users ON Users.User_ID = Articles.Article_User_ID
                WHERE Articles.Article_Level <=4 AND Articles.Article_Sticky_Date >= CurDate( ) -1            ORDER BY Articles.Article_Sticky_Date)
UNION ALL(
SELECT Articles2.Article_ID, Articles2.Article_Title, Articles2.Article_Excerpt, Articles2.Article_Content, Articles2.Article_Posted, Articles2.Article_Comments, Articles2.Article_Comments_Count, Articles2.Article_Images, Categories2.Category_ID, Categories2.Category_Title, Users2.User_ID, Users2.User_Name
       FROM (FB_Categories Categories2
          INNER JOIN FB_Articles Articles2 ON Categories.Category_ID = Articles.Article_Category_ID)
           INNER JOIN FB_Users Users2 ON Users.User_ID = Articles.Article_User_ID
            WHERE Category_Title <> 'Draft' AND Article_Level <= 4
           ORDER BY Articles.Article_Posted DESC)) IQ  ;

Use )) AS IQ if required or no IQ if that works. I have placed UNION ALL in between your sets and a DISTINCT on the outer SELECT list.

If this does not work, then remove DISTINCT from outer query and reject/do not process the duplicate primary key records in your application.
0
 
pzh20Author Commented:
Guy's, I doin't know what to say, all three suggestions didn't work! Also, I realise that I had an error in the original code which I don't think made a difference to your suggestions, but I hope you don't mind reviewing this again. To try and solve this problem I changes the second half of the union query to rename the tables just in case there was something that caused the problem, however I didn't cut and past the changes just made them manually in this posting. So, the second half should be;

SELECT Articles2.Article_ID, Articles2.Article_Title, Articles2.Article_Excerpt, Articles2.Article_Content, Articles2.Article_Posted, Articles2.Article_Comments, Articles2.Article_Comments_Count, Articles2.Article_Images, Categories2.Category_ID, Categories2.Category_Title, Users2.User_ID, Users2.User_Name
       FROM (FB_Categories Categories2
          INNER JOIN FB_Articles Articles2 ON Categories2.Category_ID = Articles2.Article_Category_ID)
           INNER JOIN FB_Users Users2 ON Users2.User_ID = Articles2.Article_User_ID
            WHERE Category_Title <> 'Draft' AND Article_Level <= 4
           ORDER BY Articles2.Article_Posted DESC)  ;


I tried these updates on all of your suggestions, but nothing sorted the second half into Posted order.

Regards
Pete
0
 
hieloCommented:
IF you review my suggestion again, I added:


at the end of the FIRST select

and
2009 as fake
at the end of the SECOND select and then order the entire result based on that fake field. Essecntially your construct should look like this:

(SELECT ..., 2010 as fake ... ORDER BY ... )
UNION
(SELECT ..., 2009 as fake ... ORDER BY ... )
ORDER BY `fake` DESC

If you use "ORDER BY `fake` DESC", then the first select should appear at  the top.
If you use "ORDER BY `fake` ASC", then the second select should  appear at the top.
0
 
k_murli_krishnaCommented:
hielo has given a good option under the circumstance to you. Basically individual queries will fetch data in the order you want but on using UNION, they will get mixed since UNION will order the entire lot trying to remove duplicates based on SELECT list & you will lose the individual order. If both your ORDER BY were on same column and of same type i.e. ASC/DESC, it would have been easier by placing an overall order by.

If there are no duplicates, then use UNION ALL and if number of records do not increase, then it is equivalent to hielo's solution. Instead of fake, simply reverse the queries i.e switch from top to bottom to get reverse order of results.
0
 
pzh20Author Commented:
Heilio,

I tried exactly your suggestion but what I get is either the first select at the front or at the end, but the second select is always mixed up.

I don't mind if the first recordset appears in the second as well, just that the first select is first and the second select is ordered by Posted date.

Regards
Pete
0
 
k_murli_krishnaCommented:
Try this single query:

SELECT Articles.Article_ID, Articles.Article_Title, Articles.Article_Excerpt, Articles.Article_Content, Articles.Article_Posted, Articles.Article_Comments, Articles.Article_Comments_Count, Articles.Article_Images, Categories.Category_ID, Categories.Category_Title, Users.User_ID, Users.User_Name
     FROM (FB_Categories Categories
         INNER JOIN FB_Articles Articles ON Categories.Category_ID = Articles.Article_Category_ID)
         INNER JOIN FB_Users Users ON Users.User_ID = Articles.Article_User_ID
                WHERE Articles.Article_Level <=4 AND Articles.Article_Sticky_Date >= CurDate( ) -1  OR (Category_Title <> 'Draft' AND Article_Level <= 4)
ORDER BY Articles.Article_Sticky_Date, Articles.Article_Posted DESC);

If this works, then performance will also improve well. Make minor corrections if required. Introduce a DISTINCT in SELECT list if required.
0
 
Ludwig DiehlSystems ArchitectCommented:
0
 
pzh20Author Commented:
k_murli_krishna I tried this but the Sticky entries were at the end.

ludwigDiehl This worked and I am accepting this answer.

Thanks to all you tried to help with this.

Pete
0
 
pzh20Author Commented:
Thanks to all you tried to help with this.

Pete
0
 
pzh20Author Commented:
Thanks for your heklp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.