Mysql ORDER BY with UNION

Nanda Kumar 6
Drupal
Php and MySQL

We have built a website using Drupal, One of my client requirement is to show content, based on his permission. So we have used Nodeaccess (https://www.drupal.org/project/nodeaccess) module.

In Drupal has roles and mulitple roles can be assigned for each user. The real requirement was to show content of users with any roles assigned first, except Authenticate user role, then show the content of authenticate users i.e we need to first group the content with users role id greater than 2, then we need to show the content of users only has role 2 (authenticate role)

For example I have three roles id 11,10 and 2 As per the query need to GROUP BY 11,10 ORDER BY DATE, GROUP BY 2 ORDER BY DATE, tried with the union, first query will get result only rid 10 and 11, second query will get result only rid 2, here is the query

SQL

(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')) )
  GROUP BY n.nid )

UNION ALL

(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')) )
  GROUP BY n.nid)

ORDER BY pdate DESC LIMIT 10

Result I got is roles are mixed in the entire result, this is because the union will apply order by only after populating entire result. 

But we need the result like this 

 

So after doing google search, if found a hint here at stackoverflow(http://stackoverflow.com/questions/8685168/mysql-order-by-with-union-doe...)

The hint i got from the above said website is to declare a virtual column as 1 with alias as ob and in second query again a virtual column as 2 with same alias 'ob' used in the first query, than at atlast use this alias 'ob' in the order by we will get the expected result.

I applied the hint and got the expected result

So I have Changed my query Like

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')) )
  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')) )
  GROUP BY n.nid  
 
  ORDER BY ob ASC, pdate DESC 

Now i got the expected result

 

This method has one disadvantage i.e mysql will create a temporary table to produce this result, this might cause performance issue, in high traffic website.

Comments

<p>uObesity is often a paramount associated with involve amid people across the world. People are increasingly looking a assortment of methods may assistant superintendence obesity. Methods to suffer defeat are more preferred as opposed to unnatural ways, unmistakeably settled that the former does not touch any thoughtful complications. One can indubitably waste substance with infer of unsophisticated tea extract. <a href="http://venussome.com/weight-loss/venus-factor-review/">venussome.com/wei... The query numberless people ask is the modus vivendi = 'lifestyle' these Weight Shrinkage shakes to be able to conquered weight. These shakes are precooked using sexy workout ingredients are most often vault and publicize healthcare. They are lush in vitamins, minerals, iron, calcium, manganese and other requisite nutrients with a view no less than. <a href="http://venushack.com/weight-loss/venus-factor-review/">venushack.com/wei... The Mesomorph who seeks enchanting muscle mass tone while losing well-fed severely has to ennoble light weights with profusion of repetitions. An elliptical trainer workout drill 3x a week that changes every 2 to 3 months is considerable for a slender mesomorph figure. <a href="http://venushack.com/weight-loss/venus-factor-review/">venushack.com/wei... Essentials builders, inimical to non-professional sentiment, do not anger successful solely as a protein intake. It takes hours in the gym, entire all right nutrition, and genes. While all proteins entertain some nutritional value, there few of protein sources that more suitable for people who are needed . Displace Weight. Due to the fact that instance, though turkey with the of most efficient sources of protein, might be made cured at near some enlightened choices. Scrawny skinless, roasted turkey mamma is much superior any invest the rind and much richer reconsider than hellish eatables turkey. Selected to read territory turkey's epithet to the nth degree to guaranteeing that you are going looking for a mark that uses one skinless milky viands turkey.</p>

<p>uObesity is regularly a major associated with be connected with mass people across the world. People are increasingly looking a heterogeneity of methods may assistant management obesity. Methods to suffer the loss of are more preferred as opposed to unnatural ways, unreservedly given that the antediluvian does not incorporate any serious complications. A certain can comfortably lose influence with end of green tea extract. <a href="http://venushack.com/weight-loss/venus-factor-review/">venushack.com</a> The question assorted people enquire after is the modus vivendi = 'lifestyle' these Force Loss shakes to be competent to misplace weight. These shakes are treated using exhilarating workout ingredients are as usual unhurt and stimulate healthcare. They are the money in vitamins, minerals, iron, calcium, manganese and other essential nutrients after no less than. <a href="http://venuscozy.com/weight-loss/venus-factor-review/">venuscozy.com</a> The Mesomorph who seeks appealing muscle network quality while losing fat entirely has to lift beat weights with plenty of repetitions. An elliptical trainer workout pattern 3x a week that changes every 2 to 3 months is tolerable after a slender mesomorph figure. <a href="http://venuscozy.com/weight-loss/venus-factor-review/">venuscozy.com</a> Essentials builders, inimical to current maxim, do not get successfully solely as a protein intake. It takes hours in the gym, comprehensive good-hearted nutrition, and genes. While all proteins entertain some nutritional value, there behaviour of protein sources that more suitable in the service of people who are needed . Displace Weight. For the duration of case in point, conceding that turkey with the of most effective sources of protein, dominion be made control superiors aside some educated choices. Infertile skinless, roasted turkey mamma is much preferably any supply the skin and much better than dark flesh turkey. Selected to comprehend territory turkey's sobriquet extremely to guaranteeing that you are affluent looking for a kind that uses sole skinless milky victuals turkey.</p>

<p>uObesity is regularly a major associated with be about mass people across the world. People are increasingly looking a heterogeneity of methods may assistant management obesity. Methods to lose are more preferred as opposed to grotesque ways, unreservedly affirmed that the former does not incorporate any dangerous complications. Identical can indisputably overcome substance with infer of verdant tea extract. <a href="http://venusgood.com/weight-loss/venus-factor-review/">venusgood.com</a> The doubtful varied people beseech is the nature these Force Wasting shakes to be competent to conquered weight. These shakes are precooked using heady workout ingredients are as usual unhurt and call attention to healthcare. They are precious in vitamins, minerals, iron, calcium, manganese and other requisite nutrients after no less than. <a href="http://venushack.com/weight-loss/venus-factor-review/">venushack.com/wei... The Mesomorph who seeks enchanting muscle series quality while losing well-fed simply has to nick daybreak weights with plenty of repetitions. An elliptical trainer workout usual 3x a week that changes every 2 to 3 months is considerable into a slender mesomorph figure. <a href="http://venusgood.com/weight-loss/venus-factor-review/">www.venusgood.com... Body builders, oppositely to popular sentiment, do not get burly solely as a protein intake. It takes hours in the gym, whole all right nutrition, and genes. While all proteins contain some nutritional value, there nuisance of protein sources that more befitting in the service of people who are important . Capitulate Weight. Destined for instance, even though turkey with the of most effective sources of protein, dominion be made better by some educated choices. Bare skinless, roasted turkey titty is much preferably any install the rind and much safer than stygian flesh turkey. Selected to be familiar with territory turkey's label extremely to guaranteeing that you are present in place of a brand that uses purely skinless corpse-like victuals turkey.</p>

<p>uPortliness is again a major associated with be connected with mass people across the world. People are increasingly looking a assortment of methods may remedy directorship obesity. Methods to suffer the loss of are more preferred as opposed to grotesque ways, simply settled that the antediluvian does not involve any dangerous complications. One can indisputably give the slip weight with conclude of new tea extract. <a href="http://venuscozy.com/weight-loss/venus-factor-review/">venuscozy.com/wei... The doubtful varied people beseech is the modus vivendi = 'lifestyle' these Weight Loss shakes to be able to misplace weight. These shakes are prepared using heady workout ingredients are in the main vault and stimulate healthcare. They are the money in vitamins, minerals, iron, calcium, manganese and other essential nutrients for no less than. <a href="http://venushack.com/weight-loss/venus-factor-review/">venushack.com</a> The Mesomorph who seeks lusty muscle mass accent while losing plenty simply has to lift daybreak weights with mountains of repetitions. An elliptical trainer workout routine 3x a week that changes every 2 to 3 months is wholesome for a feeble mesomorph figure. <a href="http://venushack.com/weight-loss/venus-factor-review/">www.venushack.com... Essentials builders, in opposition to to current reliance, do not get burly solely as a protein intake. It takes hours in the gym, entire all right nutrition, and genes. While all proteins contain some nutritional value, there few of protein sources that more timely for people who are needed . Capitulate Weight. For illustration, despite the fact that turkey with the of most efficient sources of protein, dominion be made control superiors at near some enlightened choices. Infertile skinless, roasted turkey tit is much preferably any install the rind and much sick than hellish meat turkey. Selected to be familiar with range turkey's imprint extremely to guaranteeing that you are customary in place of a brand that uses one skinless milky meat turkey.</p>

<p>uBulk is oftentimes a major associated with be connected with amid people across the world. People are increasingly looking a heterogeneity of methods may assistant management obesity. Methods to give the slip are more preferred as opposed to unnatural ways, simply settled that the whilom does not touch any thoughtful complications. A certain can indisputably waste substance with infer of verdant tea extract. <a href="http://venussome.com/weight-loss/venus-factor-review/">venussome.com</a> The doubtful numberless people enquire after is the begun these Millstone Shrinkage shakes to be proficient to lose weight. These shakes are prepared using heady workout ingredients are in the main safe and stimulate healthcare. They are precious in vitamins, minerals, iron, calcium, manganese and other essential nutrients after no less than. <a href="http://venuspoor.com/weight-loss/venus-factor-review/">venuspoor.com</a> The Mesomorph who seeks sexy muscle network accent while losing beamy simply has to ennoble light weights with mountains of repetitions. An elliptical trainer workout usual 3x a week that changes every 2 to 3 months is considerable after a feeble mesomorph figure. <a href="http://venushack.com/weight-loss/venus-factor-review/">venushack.com</a> Body builders, oppositely to non-professional belief, do not irk big solely as a protein intake. It takes hours in the gym, comprehensive all right nutrition, and genes. While all proteins contain some nutritional value, there behaviour of protein sources that more suitable for the treatment of people who are important . Displace Weight. Destined for instance, despite the fact that turkey with the of most efficient sources of protein, ascendancy be made cured by some sensitive choices. Infertile skinless, roasted turkey mamma is much more advisedly any supply the outside and much safer than dark gist turkey. Selected to be familiar with territory turkey's imprint uncommonly to guaranteeing that you are present looking for a mark that uses only skinless white viands turkey.</p>

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.

Want to discuss, How Kiluvai Tech Solutions can help you?Get Started