SQL ORDER BY Clause

The SQL ORDER BY syntax

The general syntax is:

  1. SELECT columnnames
  2. FROM tablename
  3. WHERE condition
  4. ORDER BY columnnames

 


 

SQL ORDER BY Examples

Problem: List all suppliers in alphabetical order

  1. SELECT CompanyName, ContactName, City, Country
  2. FROM Supplier
  3. ORDER BY CompanyName

The default sort order is ascending, that is, low-high or a-z.
Results: 29 records

Id CompanyName ContactName City Country
18 Aux joyeux ecclésiastiques Guylène Nodier Paris France
16 Bigfoot Breweries Cheryl Saylor Bend USA
5 Cooperativa de Quesos ‘Las Cabras’ Antonio del Valle Saavedra Oviedo Spain
27 Escargots Nouveaux Marie Delamare Montceau France
1 Exotic Liquids Charlotte Cooper London UK

 


Problem: List all suppliers in reverse alphabetical order

  1. SELECT CompanyName, ContactName, City, Country
  2. FROM Supplier
  3. ORDER BY CompanyName DESC

The keyword DESC denotes descending, i.e., reverse order.
Results: 29 records

Id CompanyName ContactName City Country
22 Zaanse Snoepfabriek Dirk Luchte Zaandam Netherlands
4 Tokyo Traders Yoshi Nagase Tokyo Japan
17 Svensk Sjöföda AB Michael Björn Stockholm Sweden
8 Specialty Biscuits, Ltd. Peter Wilson Manchester UK
10 Refrescos Americanas LTDA Carlos Diaz Sao Paulo Brazil

 


Problem: List all customers ordered by country, then by city within each country
Ordering by one or more columns is possible.

  1. SELECT FirstName, LastName, City, Country
  2. FROM Customer
  3. ORDER BY Country, City

Results: 91 records

Id FirstName LastName City Country
12 Patricio Simpson Buenos Aires Argentina
54 Yvonne Moncada Buenos Aires Argentina
64 Sergio Gutiérrez Buenos Aires Argentina
20 Roland Mendel Graz Austria
59 Georg Pipps Salzburg Austria
50 Catherine Dewey Bruxelles Belgium
76 Pascale Cartrain Charleroi Belgium

 


Problem: List all suppliers in the USA, Japan, and Germany, ordered by city,
then by company name in reverse order

  1. SELECT Id, CompanyName, City, Country
  2. FROM Supplier
  3. WHERE Country IN (‘USA’, ‘Japan’, ‘Germany’)
  4. ORDER BY Country ASC, CompanyName DESC

This shows that you can order by more than one column.
ASC denotes ascending, but is optional as it is the default sort order.
Results: 9 records

Id CompanyName City Country
12 Plutzer Lebensmittelgroßmärkte AG Frankfurt Germany
13 Nord-Ost-Fisch Handelsgesellschaft mbH Cuxhaven Germany
11 Heli Süßwaren GmbH & Co. KG Berlin Germany
4 Tokyo Traders Tokyo Japan
6 Mayumi’s Osaka Japan
2 New Orleans Cajun Delights New Orleans USA
19 New England Seafood Cannery Boston USA
3 Grandma Kelly’s Homestead Ann Arbor USA
16 Bigfoot Breweries Bend USA

 


Problem: Show all orders, sorted by total amount, the largest first, within each year

  1. SELECT Id, OrderDate, CustomerId, TotalAmount
  2. FROM [Order]
  3. ORDER BY YEAR(OrderDate) ASC, TotalAmount DESC

Note: DESC means descending, but is optional as it is the default sort order.
[Order] must be bracketed because it also is a keywork in SQL.
Results: 830 records.

Id OrderDate CustomerId TotalAmount
125 2012-12-04 00:00:00.000 62 12281.20
106 2012-11-13 00:00:00.000 59 10741.60
113 2012-11-22 00:00:00.000 7 7390.20
144 2012-12-23 00:00:00.000 17 86.40
24 2012-08-01 00:00:00.000 75 48.00
177 2013-01-23 00:00:00.000 51 11493.20
170 2013-01-16 00:00:00.000 73 11283.20
560 2013-12-31 00:00:00.000 27 18.40
535 2013-12-17 00:00:00.000 12 12.50
618 2014-02-02 00:00:00.000 63 17250.00
783 2014-04-17 00:00:00.000 71 16321.90

Notice the year breakpoints: 2012 – 2013 and 2013 – 2014. Each year starts with the highest TotalAmounts.
This shows that other data types, such as numbers, dates, and bits can also be sorted.
Note: YEAR is a built-in function which returns the year from a date.

“SQL ORDER BY Clause”의 186개의 댓글

  1. I do not know whether it’s just me or if perhaps everybody else experiencing issues with your blog.
    It looks like some of the written text in your content are running off the screen. Can someone else please comment and let me
    know if this is happening to them too? This may be
    a issue with my browser because I’ve had this happen before.

    Thank you

  2. Write more, thats all I have to say. Literally, it seems as though you
    relied on the video to make your point. You definitely
    know what youre talking about, why waste your intelligence on just posting videos to your site when you could be giving
    us something enlightening to read?

  3. hey there and thank you for your info – I have certainly picked
    up anything new from right here. I did however expertise several technical issues using this site, since I experienced to
    reload the web site a lot of times previous to I could get it to load correctly.
    I had been wondering if your web host is OK? Not that I am complaining, but slow loading instances times will
    often affect your placement in google and could damage your high quality score if advertising and marketing
    with Adwords. Well I am adding this RSS to my e-mail and
    can look out for a lot more of your respective exciting content.

    Make sure you update this again very soon.

  4. I simply want to say I’m very new to blogging and site-building and honestly enjoyed this website. More than likely I’m likely to bookmark your blog . You surely come with exceptional well written articles. Appreciate it for revealing your webpage.

  5. I like what you guys are up too. Such intelligent work and reporting! Keep up the excellent works guys I’ve incorporated you guys to my blogroll. I think it’ll improve the value of my web site :).

  6. I simply want to mention I’m very new to blogs and definitely loved you’re page. Probably I’m planning to bookmark your site . You surely have outstanding well written articles. Thank you for revealing your webpage.

  7. I think this is one of the most important info for me. And i am glad reading your article. But should remark on few general things, The web site style is great, the articles is really nice : D. Good job, cheers

  8. My wife and i ended up being very comfortable John could conclude his researching via the precious recommendations he received from your web page. It’s not at all simplistic to just happen to be offering key points others might have been selling. We understand we have you to thank for this. Most of the illustrations you have made, the straightforward site menu, the friendships you will give support to instill – it’s got most wonderful, and it is aiding our son in addition to the family reason why this content is satisfying, and that’s truly serious. Thank you for the whole thing!

  9. Having read this I believed it was extremely enlightening. I appreciate you spending some time and energy to put this content together. I once again find myself spending way too much time both reading and leaving comments. But so what, it was still worth it!

  10. Hello, I think your website might be having web browser compatibility issues. Whenever I look at your website in Safari, it looks fine however, when opening in IE, it has some overlapping issues. I just wanted to provide you with a quick heads up! Other than that, great website!

  11. I just want to say I’m beginner to blogging and absolutely loved you’re blog. Almost certainly I’m going to bookmark your website . You definitely come with really good articles and reviews. Bless you for sharing your webpage.

  12. Thanks for the guidelines shared using your blog. Something also important I would like to talk about is that fat loss is not information about going on a fad diet and trying to get rid of as much weight as you can in a few days. The most effective way to burn fat is by acquiring it gradually and obeying some basic recommendations which can help you to make the most out of your attempt to lose fat. You may realize and be following these tips, yet reinforcing knowledge never affects.

  13. I’m often to running a blog and i actually respect your content. The article has actually peaks my interest. I am going to bookmark your site and keep checking for new information.

  14. Having read this I believed it was rather informative. I appreciate you taking the time and effort to put this article together. I once again find myself personally spending a significant amount of time both reading and leaving comments. But so what, it was still worth it.

  15. Hello there! I could have sworn I’ve visited this website before but after going through a few of the posts I realized it’s new to me. Anyways, I’m definitely delighted I found it and I’ll be book-marking it and checking back frequently!

  16. Having read this I thought it was rather informative. I appreciate you taking the time and energy to put this short article together. I once again find myself spending a lot of time both reading and commenting. But so what, it was still worthwhile!

  17. Your style is so unique in comparison to other people I have read stuff from. Many thanks for posting when you’ve got the opportunity, Guess I will just book mark this page.

  18. Yet another thing to mention is that an online business administration diploma is designed for people to be able to without problems proceed to bachelor degree programs. The 90 credit education meets the lower bachelor college degree requirements and when you earn your associate of arts in BA online, you will have access to the most up-to-date technologies in such a field. Several reasons why students would like to get their associate degree in business is because they’re interested in the field and want to obtain the general schooling necessary just before jumping right bachelor college diploma program. Thx for the tips you actually provide in your blog.

  19. I don’t even know how I ended up here, but I thought this post was great. I do not know who you are but definitely you are going to a famous blogger if you are not already 😉 Cheers!

  20. Hello there, simply turned into alert to your weblog via Google, and located that it is really informative. I am gonna be careful for brussels. I’ll be grateful if you happen to proceed this in future. Many folks can be benefited from your writing. Cheers!

  21. Right here is the right site for anyone who hopes to find out about this topic. You understand so much its almost hard to argue with you (not that I really will need to…HaHa). You certainly put a new spin on a subject which has been discussed for decades. Wonderful stuff, just excellent.

  22. You’re so interesting! I don’t believe I’ve read something like this before. So great to find someone with a few unique thoughts on this topic. Really.. thanks for starting this up. This web site is something that is needed on the internet, someone with a little originality!

  23. Hi there very nice website!! Guy .. Excellent .. Wonderful .. I’ll bookmark your website and take the feeds also…I’m satisfied to find a lot of helpful info right here within the publish, we’d like work out extra techniques in this regard, thanks for sharing.

  24. Hello there! This blog post couldn챠t be written any better! Reading through this article reminds me of my previous roommate! He continually kept talking about this. I am going to send this information to him. Fairly certain he’ll have a very good read. Thanks for sharing!

  25. After I initially left a comment I appear to have clicked on the -Notify me when new comments are added- checkbox and from now on each time a comment is added I receive 4 emails with the exact same comment. Is there a means you can remove me from that service? Thanks a lot.

  26. Aw, this was a really good post. Spending some time and actual effort to generate a very good article… but what can I say… I hesitate a lot and never seem to get anything done.

  27. You can definitely see your skills in the work you write. The arena hopes for even more passionate writers such as you who are not afraid to say how they believe. At all times follow your heart.

  28. I think this is among the most significant info for me. And i’m glad reading your article. But wanna remark on some general things, The web site style is ideal, the articles is really great : D. Good job, cheers

  29. You actually make it seem so easy with your presentation but I find this matter to be really something which I think I would never understand. It seems too complicated and extremely broad for me. I am looking forward for your next post, I’ll try to get the hang of it!

  30. Oh my goodness! Amazing article dude! Thank you, However I am encountering troubles with your RSS. I don’t know the reason why I cannot subscribe to it. Is there anyone else getting the same RSS issues? Anybody who knows the answer will you kindly respond? Thanks!!

  31. I truly love your site.. Pleasant colors & theme. Did you build this site yourself? Please reply back as I’m trying to create my very own website and would like to find out where you got this from or exactly what the theme is named. Kudos!

  32. Hello there! This is kind of off topic but I need some guidance from an established blog. Is it very difficult to set up your own blog? I’m not very techincal but I can figure things out pretty fast. I’m thinking about making my own but I’m not sure where to begin. Do you have any ideas or suggestions? Many thanks

  33. Thanks for the sensible critique. Me and my neighbor were just preparing to do some research on this. We got a grab a book from our area library but I think I learned more from this post. I’m very glad to see such excellent information being shared freely out there.

  34. What’s Happening i am new to this, I stumbled upon this I have discovered It positively useful and it has aided me out loads. I’m hoping to give a contribution & aid different users like its helped me. Great job.

  35. I’m very pleased to discover this page. I want to to thank you for ones time due to this wonderful read!! I definitely really liked every bit of it and i also have you saved as a favorite to see new things on your website.

  36. This is a correct weblog for anyone who wishes to be familiar with this topic. You are aware of a lot its almost tricky to argue together with you (not too I really would want…HaHa). You definitely put a brand new spin over a topic thats been written about for many years. Great stuff, just great!

  37. Wow! This can be one particular of the most useful blogs We’ve ever arrive across on this subject. Basically Wonderful. I’m also an expert in this topic therefore I can understand your effort.

  38. I definitely wanted to write a simple message so as to say thanks to you for all of the nice ways you are sharing here. My long internet investigation has at the end of the day been honored with incredibly good insight to write about with my family. I ‘d declare that many of us readers actually are very blessed to exist in a perfect website with many perfect professionals with very helpful tactics. I feel very privileged to have encountered the web site and look forward to so many more cool moments reading here. Thanks once more for a lot of things.

  39. An outstanding share! I’ve just forwarded this onto a co-worker who was conducting a little research on this. And he in fact bought me breakfast simply because I stumbled upon it for him… lol. So allow me to reword this…. Thanks for the meal!! But yeah, thanks for spending the time to discuss this issue here on your web site.

  40. Oh my goodness! Awesome article dude! Thank you so much, However I am encountering troubles with your RSS. I don’t know why I can’t subscribe to it. Is there anybody else having similar RSS problems? Anybody who knows the answer can you kindly respond? Thanx!!

  41. An outstanding share! I’ve just forwarded this onto a coworker who was conducting a little research on this. And he actually ordered me dinner due to the fact that I discovered it for him… lol. So let me reword this…. Thank YOU for the meal!! But yeah, thanx for spending some time to discuss this matter here on your web page.

  42. Can I simply just say what a comfort to find someone who genuinely knows what they are talking about on the web. You certainly realize how to bring a problem to light and make it important. More and more people must read this and understand this side of the story. I was surprised that you are not more popular given that you most certainly possess the gift.

  43. An intriguing discussion is definitely worth comment. I do think that you ought to publish more on this issue, it might not be a taboo subject but typically folks don’t speak about these topics. To the next! Many thanks!!

  44. After looking at a number of the articles on your website, I truly like your technique of writing a blog. I saved as a favorite it to my bookmark website list and will be checking back soon. Please check out my website too and let me know what you think.

  45. I’d like to thank you for the efforts you’ve put in writing this site. I really hope to view the same high-grade blog posts by you later on as well. In truth, your creative writing abilities has motivated me to get my own site now 😉

  46. A motivating discussion is worth comment. I do think that you ought to write more about this subject, it may not be a taboo matter but generally folks don’t talk about these topics. To the next! Many thanks!!

  47. An outstanding share! I have just forwarded this onto a co-worker who has been conducting a little homework on this. And he actually bought me dinner simply because I discovered it for him… lol. So allow me to reword this…. Thanks for the meal!! But yeah, thanx for spending the time to discuss this issue here on your website.

  48. I really love your site.. Great colors & theme. Did you develop this amazing site yourself? Please reply back as I’m trying to create my very own website and would love to learn where you got this from or exactly what the theme is called. Appreciate it!

  49. Hello there! I could have sworn I’ve been to this site before but after browsing through many of the posts I realized it’s new to me. Anyhow, I’m certainly pleased I stumbled upon it and I’ll be bookmarking it and checking back frequently!

댓글 남기기