메뉴 닫기

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.

10 Comments

  1. g https://tinyurl.com/rsacwgxy

    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. http://tinyurl.com/

    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. cbd oil that works 2020

    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.

댓글 남기기

이메일은 공개되지 않습니다.