2

I'm trying to do multiple selects to give multiple ordered columns. I'm struggling to get the below mysql query to achieve the output I want.

SQLfiddle: http://www.sqlfiddle.com/#!2/a1002/3

Table of example data table, veh_summary :

veh_id    fac_id   item_id    w_name    stat
   1        1         51      name1      45
   1        1         52      name2      10
   1        1         53      name3      2
   1        2         51      name1      28
   1        2         52      name2      17
   1        2         53      name3      67
   1        3         51      name1      0
   1        3         52      name2      44
   1        3         53      name3      33
   2        1         51      name1      26

Output I want where each multiple select are ordered desc with limit:

item_id_1   w_name_1   stat_1   item_id_2   w_name_2   stat_2 
    51        name1      45         53        name3      67
    52        name2      10         51        name1      28

I'm trying this query:

select a.item_id as item_id_1, a.w_name as w_name_1, a.stat as stat_1,
   b.item_id as item_id_2, b.w_name as w_name_2, b.stat as stat_2
from
  (select item_id, w_name, stat from veh_summary 
      where veh_id = 1 and fac_id = 1 ) a,
  (select item_id, w_name, stat from veh_summary 
      where veh_id = 1 and fac_id = 2 ) b 
  order by stat_1 desc, stat_2 desc limit 2 

But get this output where the first select is not ordering desc:

item_id_1   w_name_1   stat_1   item_id_2   w_name_2   stat_2 
    51        name1       45        53         name3     67
    51        name1       45        51         name1     28

I'm sure this is simple to do, I've also tried the order by limit in the select itself. I've done a bit of searching through the questions but don't seem to see one that fits what i'm looking for.

  • 1
    This is simple to do, true, with window functions and a FULL join. MySQL has neither. – ypercubeᵀᴹ Jul 8 '14 at 15:12
  • Hmmm ok then, that's annoying...i'm going to have to figure out another way to do this. Thanks for the reply ypercube. – dave12345 Jul 8 '14 at 15:59
  • The simplest would be to run 2 queries and combine the results in the application layer. It can be done in MySQL but it won't be very pretty. – ypercubeᵀᴹ Jul 8 '14 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.