CODEWITHSUNDEEP
mysql
Exception
Exception

Reputation: 8379

Why ORDER BY on integer column is not working as expected

I have a table schema like below 

CREATE TABLE test( qid varchar(255), qor int );

And I am trying to retrieve QID order by qor like below 

SELECT DISTINCT qid from test ORDER BY qor + 0;

But it is not working as I expect it to sort qor first and then retrieve distinct qid values from selection. please have a look into sqlfiddle

Upvotes: 0

Views: 687

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 415735

I expect it to sort qor first and then retrieve distinct qid values

That's not how it works. Distinct values are handled first, order is handled later. Otherwise, imagine this data:

qid    qor
'foo'    0
'bar'    1
'foo'    2

If it sorted by qor first (already done here), what would the order of the resulting records be? Is foo before bar, or after?    "Yes."       As you can see here and in the comments to another answer, the first three people to respond each interpreted it in a different way: one SUM, one MAX, and one MIN. That's no good.

If MySql followed ansi rules, what you have wouldn't even be legal sql, because after grouping by qid (which is what a distinct operator does) there is no way to know which qor value was used for the result. ANSI SQL requires any columns used after an aggregate operation (like distinct) be part of the aggregation, for reasons like the one I just demonstrated.

What you can do is something like this:

SELECT qid 
FROM test 
GROUP BY qid
ORDER BY MIN(qor);

Of course, MAX, AVG, etc would also work instead of MIN, depending on how you wanted to handle that situation.

Upvotes: 2

juergen d
juergen d

Reputation: 204756

You want to group by the qid. You need an aggregate function on the qor column. I assume you want to sum up all qor for the qids

SELECT qid, sum(qor) as total_qor
from test 
group by qid
ORDER BY total_qor desc;

SQLFiddle demo

An alternative would be to sort by the highest qor. Then use max() instead of sum()

Upvotes: 2

Related Questions