Praveen Srinivasan Praveen Srinivasan - 4 months ago 10
SQL Question

Mysql multiple ORDER BY with UNION

I want to get data with order by date. I am using union.

I have gone through the below url. But I am not getting clear solution with that.

mysql order by with union doesn't seem to work

My Query

(SELECT n.nid,
Max(na.gid) AS mid,
fav.field_date_posted_value AS pdate
FROM `node` AS n
JOIN nodeaccess AS na
ON na.nid = n.nid
LEFT JOIN field_data_field_date_posted AS fav
ON fav.entity_id = n.nid
WHERE ( na.gid IN( 10, 11 )
AND ( n.status = '1' )
AND ( n.type IN ( 'article', 'blog', 'events', 'media',
'press_releases', 'expert_speak', 'feature',
'case_study',
'news', 'the_igtb_series', 'trend', 'white_paper' )
) )
GROUP BY n.nid
ORDER BY pdate DESC)

UNION

(SELECT n.nid,
Max(na.gid) AS mid,
fav.field_date_posted_value AS pdate
FROM `node` AS n
JOIN nodeaccess AS na
ON na.nid = n.nid
LEFT JOIN field_data_field_date_posted AS fav
ON fav.entity_id = n.nid
WHERE ( na.gid IN( 2 )
AND ( n.status = '1' )
AND ( n.type IN ( 'article', 'blog', 'events', 'media',
'press_releases', 'expert_speak', 'feature',
'case_study',
'news', 'the_igtb_series', 'trend', 'white_paper' )
) )
GROUP BY n.nid
ORDER BY pdate DESC)
LIMIT
10


My Result

+-------+------+---------------------+
| nid | mid | pdate |
+-------+------+---------------------+
| 12472 | 10 | 2015-05-11 00:00:00 |
| 12473 | 10 | 2015-04-03 00:00:00 |
| 12475 | 10 | 2015-06-08 00:00:00 |
| 12476 | 10 | 2015-12-15 01:55:48 |
| 12477 | 10 | 2014-06-30 00:00:00 |
| 12478 | 10 | 2013-12-26 00:00:00 |
| 12482 | 10 | 2014-02-02 00:00:00 |
| 12483 | 10 | 2014-09-01 00:00:00 |
| 12484 | 10 | 2015-12-04 00:00:00 |
| 12485 | 10 | 2015-08-14 00:00:00 |
+-------+------+---------------------+


In the above url the date order is not working. I don't want to make order by as common. I need separate order by for both select queries (Used in UNION).

Update 2:

(SELECT n.nid, max(na.gid) as mid, fav.field_date_posted_value as pdate FROM `node` as n
JOIN nodeaccess AS na ON na.nid = n.nid
LEFT JOIN field_data_field_date_posted AS fav ON fav.entity_id = n.nid
WHERE (na.gid IN(10,11) AND (n.status = '1') AND (n.type IN ('article','blog', 'events', 'media', 'press_releases', 'expert_speak', 'feature', 'case_study', 'news', 'the_igtb_series', 'trend', 'white_paper')) )
GROUP BY n.nid
ORDER BY pdate DESC)

UNION

(SELECT n.nid, max(na.gid) as mid, fav.field_date_posted_value as pdate FROM `node` as n
JOIN nodeaccess AS na ON na.nid = n.nid
LEFT JOIN field_data_field_date_posted AS fav ON fav.entity_id = n.nid
WHERE (na.gid IN(2) AND (n.status = '1') AND (n.type IN ('article','blog', 'events', 'media', 'press_releases', 'expert_speak', 'feature', 'case_study', 'news', 'the_igtb_series', 'trend', 'white_paper')) )
GROUP BY n.nid
ORDER BY pdate DESC)

ORDER BY pdate DESC LIMIT 10


The above gives the below result.

+-------+------+---------------------+
| nid | mid | pdate |
+-------+------+---------------------+
| 12789 | 11 | 2016-09-26 00:00:00 |
| 12826 | 2 | 2016-07-13 00:00:00 |
| 12845 | 2 | 2016-07-05 00:00:00 |
| 12823 | 10 | 2016-06-21 00:00:00 |
| 12822 | 2 | 2016-06-17 00:00:00 |
| 12821 | 10 | 2016-06-07 00:00:00 |
| 12635 | 10 | 2016-06-07 00:00:00 |
| 12821 | 2 | 2016-06-07 00:00:00 |
| 12633 | 10 | 2016-05-25 02:19:29 |
| 12548 | 10 | 2016-05-20 00:00:00 |
+-------+------+---------------------+


In this result I don't want to reorder the column "mid"

Answer

Try this

SELECT n.nid, max(na.gid) as mid,fav.field_date_posted_value, UNIX_TIMESTAMP(fav.field_date_posted_value) as pdate, 1 as ob FROM `node` as n
JOIN nodeaccess AS na ON na.nid = n.nid
LEFT JOIN field_data_field_date_posted AS fav ON fav.entity_id = n.nid
WHERE (na.gid IN(10,11) AND (n.status = '1') AND (n.type IN  ('article','blog', 'events', 'media', 'press_releases', 'expert_speak', 'feature', 'case_study', 'news', 'the_igtb_series', 'trend', 'white_paper')) )
GROUP BY n.nid 


UNION ALL

SELECT n.nid, max(na.gid) as mid,fav.field_date_posted_value, UNIX_TIMESTAMP(fav.field_date_posted_value) as pdate, 2 as ob FROM `node` as n
JOIN nodeaccess AS na ON na.nid = n.nid
LEFT JOIN field_data_field_date_posted AS fav ON fav.entity_id = n.nid
WHERE (na.gid IN(2) AND (n.status = '1') AND (n.type IN  ('article','blog', 'events', 'media', 'press_releases', 'expert_speak', 'feature', 'case_study', 'news', 'the_igtb_series', 'trend', 'white_paper')) )
GROUP BY n.nid  

ORDER BY ob ASC, pdate DESC

Fixed the order by date only base or group

Comments