I have a query listed below:
1.
select priority_level, ID from product order by priority_level, ID DESC
The result will be:
priority_level ID
9 20086750
93 20086845
100 20087773
100 20086884
100 20086843
2. select priority_level, ID from product order by priority_level, ID ASC
priority_level ID
9 20086750
93 20086845
100 20086843
100 20086884
100 20087773
3.
select priority_level, ID from product order by priority_level DESC
priority_level ID
100 20087773
100 20086843
100 20086884
93 20086845
9 20086750
Why the resultset of case 1 and case 2 are the same for DESC and ASC? After removing "order by ID", like case 3, then I get the order I want. Could someone explain this? What's wrong with order by priority_level, ID together? Thanks.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
As an addition to angelIII comment:
" After removing "order by ID", like case 3, then I get the order I want." -> this is only random output and cannot be generalized. If you want ordered output then you must use ORDER BY. More about it you can read here:
http://asktom.oracle.com/p
When you specify two or more sort columns in an ORDER BY Clause, the columns are not treated equally. The first column you name is the primary sort column and it will detemine all sorting until it reaches two or more records that have the same value in that column. Then the second sort column is applied to those records. if any ot them have identical values in the secord sort column, orcle looks see if you have more defined a third sort column and so on....
I dont see any problem here...
just on 3,
select priority_level, ID from product order by priority_level DESC
priority_level ID
100 20087773
100 20086843
100 20086884
93 20086845
9 20086750
it is sorted by level descending, if levels are same, probably oracle will give you physical order (first in first out)
if you want to sort by id too, use
select priority_level, ID from product order by priority_level DESC, ID DESC
100 20086884
100 20086843
100 20087773
93 20086845
9 20086750
Business Accounts
Answer for Membership
by: angelIIIPosted on 2009-10-13 at 22:46:54ID: 25567614
the ASC/ DESC can be applied for each of the columns to be sorted ... aka:
Select allOpen in new window