sorting 2 Columns in mysql

Question

i Have Following Mysql Query

SELECT DISTINCT
  main_Table.id,
  t1.percentage,
  t2.percentage
FROM image_new main_Table
  INNER JOIN (SELECT DISTINCT
    image_colors_new.image_id,
    image_colors_new.percentage
  FROM image_colors_new
  WHERE image_colors_new.s BETWEEN 80 AND 120 AND image_colors_new.h BETWEEN - 20 AND 20 AND image_colors_new.v BETWEEN 73 AND 103
  GROUP BY image_colors_new.percentage
  ORDER BY image_colors_new.percentage DESC) t1
    ON main_Table.id = t1.image_id
  INNER JOIN (SELECT DISTINCT
    image_colors_new.image_id,
    image_colors_new.percentage
  FROM image_colors_new
  WHERE image_colors_new.s BETWEEN - 20 AND 20 AND image_colors_new.h BETWEEN - 20 AND 20 AND image_colors_new.v BETWEEN 85 AND 115
  GROUP BY image_colors_new.percentage
  ORDER BY image_colors_new.percentage DESC) t2
    ON main_Table.id = t2.image_id

This Gives me Following Out Put

   id   percentage      percentage1
36888   32.975669099756 0.559610705596
54899   9.9722991689751 0.55401662049861
43584   9.9195710455764 9.3833780160858
61517   9.7938144329897 40.20618556701
78076   9.7267759562842 12.786885245902
52123   9.6916299559471 3.3039647577093
36378   9.5798319327731 18.655462184874
43820   9.5238095238095 10.31746031746
53579   9.4736842105263 5.6140350877193

but i want rows in following format i.e rows that have percentage with highest value must pop to top, from either of columns

id      percentage      percentage1
61517   9.7938144329897 40.20618556701
36888   32.975669099756 0.559610705596
36378   9.5798319327731 18.655462184874
78076   9.7267759562842 12.786885245902
43820   9.5238095238095 10.31746031746
43584   9.9195710455764 9.3833780160858
54899   9.9722991689751 0.55401662049861
52123   9.6916299559471 3.3039647577093
53579   9.4736842105263 5.6140350877193

Probably something like GROUP BY id ORDER BY MAX(percentage, percentage1) — ceejayoz
– ceejayoz, Commented Jan 12, 2015 at 13:53
@JanDoggen: That is the problem if already know that then simple order by is the easiest — temp-learn
– temp-learn, Commented Jan 12, 2015 at 13:54
@ceejayoz, does MAX really work here, isn't that (only) an aggregate function? — jarlh
– jarlh, Commented Jan 12, 2015 at 13:54
@jarlh That's why GROUP BY id. Turns it into an aggregate without breaking anything. — ceejayoz
– ceejayoz, Commented Jan 12, 2015 at 14:02

Gordon Linoff · Accepted Answer · 2015-01-12 13:53:30Z

4

It sounds like you want to order by the larger of the percentage values. Add:

order by greatest(t1.percentage, t2.percentage) desc;

This should actually go in the outer query. Don't get in the habit of doing order by in subqueries. It might usually work, but there are no guarantees (and this is particularly true in other databases in multi-threaded implementations).

answered Jan 12, 2015 at 13:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

jarlh Over a year ago

ORDER BY in sub-queries does however make sense when combined with TOP/LIMIT/FETCH FIRST etc.

temp-learn Over a year ago

@jarlh: Agreed too, but for my scenario where i simply need kinda max of any of 2 i have this as best case.

temp-learn Over a year ago

@Gordon Linoff: This Is not producing the desired result.

Gordon Linoff Over a year ago

@temp-learn . . . How is it not working? Note that you have to put the order by in the outer query because the joins can and will change the order of the result set.

temp-learn Over a year ago

@GordonLinoff:Mate i didnt said that its not working , it is working but out put is not what i desire. And as you said(quite logically) that the clause should be applied to the outer query, and so it was applied to outer query. I will try to paste the out put below.

Collectives™ on Stack Overflow

sorting 2 Columns in mysql

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related