MySQL ORDER BY multiple columns from same multiple tables

Question

So my problem is I'm trying to use ORDER BY on columns from multiple tables, the multiple tables are always the same one.

My SQL statement:

SELECT
n0.val AS n312, 
n1.val AS n309, 
c.food_name, 
f.name, 
FROM contents n0, contents n1, contents c
LEFT JOIN foods f ON f.id = c.food_id     
WHERE n0.url = c.url AND n0.orig_source_id = 312 AND n1.url = c.url AND n1.orig_source_id = 309
GROUP BY c.food_name
ORDER BY n312 DESC, n309 DESC
LIMIT 30

The problem is here only the n312 is ordered and not the n309, they both should be ordered right? I assume this is a problem with the multiple tables in the query but not sure how I can fix this. The values are both decimals.

Any help will be appreciated, thanks.

Sazzadur Rahaman · Accepted Answer · 2018-07-14 02:06:07Z

1

This is working as expected. ORDER BY is first ordering your table with column n312 and then with column n309. Suppose you have a table like below:

 n312 | n309
-----------
 2.0 | 1.0
 5.0 | 2.0
 2.0 | 3.0
 3.0 | 2.0
 5.0 | 1.0

Then, The "ORDER BY" will arrange your table like below:

n312 | n309
-----------
 5.0 | 2.0
 5.0 | 1.0
 3.0 | 2.0
 2.0 | 3.0
 2.0 | 1.0

It first orders your rows by n312. If for two rows n312 have the same value (e.g., row 1 and row 2), then it will order them with the value of column n309.

If you want something like:

n312 | n309
-----------
 5.0 | 3.0
 5.0 | 2.0
 3.0 | 2.0
 2.0 | 1.0
 2.0 | 1.0

Then you have to something similar like below:

 SELECT t1.n312, t2.n309 from 
   (select row_number() as id, n312 from t ORDER BY n312) as t1 
   JOIN 
   (select row_number() as id, n309 from t ORDER BY n309) as t2 
   WHERE t1.id = t2.id;

Idea is to order the table individually with both of the columns first and then select the ordered columns.

edited Jul 14, 2018 at 2:06

answered Jul 14, 2018 at 1:29

Sazzadur Rahaman

7,1161 gold badge33 silver badges53 bronze badges

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

2 Comments

Joel Over a year ago

Hmm.. so how would I order by both of the columns? For example order by the highest of both of the columns first instead of just the one column, is it not possible?

Sazzadur Rahaman Over a year ago

I updated my answer. The query might not be the exact one but something similar will work.

Djonathan Krause · Accepted Answer · 2018-07-14 01:21:48Z

0

Yeah, I think that should work...

Try this:

SELECT * FROM (
SELECT n0.val AS n312, n1.val AS n309, c.food_name, f.name, 
FROM contents n0, contents n1, contents c
    LEFT JOIN foods f 
        ON f.id = c.food_id     
WHERE n0.url = c.url 
AND n0.orig_source_id = 312 
AND n1.url = c.url 
AND n1.orig_source_id = 309
GROUP BY c.food_name
ORDER BY n312 DESC, n309 DESC
LIMIT 30
) ORDER BY n312 DESC, n309 DESC

answered Jul 14, 2018 at 1:21

Djonathan Krause

1243 silver badges14 bronze badges

1 Comment

Joel Over a year ago

Nope, the same result, it's strange and I can't figure out what's wrong...

Collectives™ on Stack Overflow

MySQL ORDER BY multiple columns from same multiple tables

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related