Which syntax should you use to sort the result set in reverse alphabetic order.

Problem: List all suppliers in alphabetical order.

SELECT Id, CompanyName, ContactName, City, Country
  FROM Supplier
 ORDER BY CompanyName

Try it live

The default sort order is ascending, that is, low-high or a-z.

IdCompanyNameContactNameCityCountry18Aux joyeux ecclésiastiquesGuylène NodierParisFrance16Bigfoot BreweriesCheryl SaylorBendUSA5Cooperativa de Quesos 'Las Cabras'Antonio del Valle SaavedraOviedoSpain27Escargots NouveauxMarie DelamareMontceauFrance1Exotic LiquidsCharlotte CooperLondonUK

ORDER BY, descending order

SUPPLIERIdCompanyNameContactNameCityCountryPhoneFax

Problem: List all suppliers in reverse alphabetical order.

SELECT Id, CompanyName, ContactName, City, Country
  FROM Supplier
 ORDER BY CompanyName DESC

Try it live

The keyword DESC denotes descending, i.e., reverse order.

IdCompanyNameContactNameCityCountry22Zaanse SnoepfabriekDirk LuchteZaandamNetherlands4Tokyo TradersYoshi NagaseTokyoJapan17Svensk Sjöföda ABMichael BjörnStockholmSweden8Specialty Biscuits, Ltd.Peter WilsonManchesterUK10Refrescos Americanas LTDACarlos DiazSao PauloBrazil

ORDER BY, multiple columns

CUSTOMERIdFirstNameLastNameCityCountryPhone

Problem: List all customers ordered by country, then by city within each country.

SELECT FirstName, LastName, City, Country
  FROM Customer
 ORDER BY Country, City

Try it live

IdFirstNameLastNameCityCountry12PatricioSimpsonBuenos AiresArgentina54YvonneMoncadaBuenos AiresArgentina64SergioGutiérrezBuenos AiresArgentina20RolandMendelGrazAustria59GeorgPippsSalzburgAustria50CatherineDeweyBruxellesBelgium76PascaleCartrainCharleroiBelgium

As you can see, sorting by multiple columns is entirely possible.

ORDER BY, ascending and descending

SUPPLIERIdCompanyNameContactNameCityCountryPhoneFax

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

SELECT Id, CompanyName, City, Country
  FROM Supplier
 WHERE Country IN ('USA', 'Japan', 'Germany')
 ORDER BY Country ASC, CompanyName DESC

Try it live

This shows that you can order by more than one column, each one in ascending or descending sort order.

IdCompanyNameCityCountry12Plutzer Lebensmittelgroßmärkte AGFrankfurtGermany13Nord-Ost-Fisch Handelsgesellschaft mbHCuxhavenGermany11Heli Süßwaren GmbH & Co. KGBerlinGermany4Tokyo TradersTokyoJapan6Mayumi'sOsakaJapan2New Orleans Cajun DelightsNew OrleansUSA19New England Seafood CanneryBostonUSA3Grandma Kelly's HomesteadAnn ArborUSA16Bigfoot BreweriesBendUSA

ORDERIdOrderDateOrderNumberCustomerIdTotalAmount

Problem: List all orders, sorted by amount (largest first), within each year.

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

Try it live

DESC denotes descending sort order.
The table name [Order] is bracketed because it is a keyword in SQL.

IdOrderDateCustomerIdTotalAmount1252012-12-04 00:00:00.0006212281.201062012-11-13 00:00:00.0005910741.601132012-11-22 00:00:00.00077390.20

1442012-12-23 00:00:00.0001786.40242012-08-01 00:00:00.0007548.001772013-01-23 00:00:00.0005111493.201702013-01-16 00:00:00.0007311283.205602013-12-31 00:00:00.0002718.405352013-12-17 00:00:00.0001212.506182014-02-02 00:00:00.0006317250.007832014-04-17 00:00:00.0007116321.90

The results show the following year breakpoints: 2012 - 2013 and 2013 - 2014. Each year starts with the highest TotalAmounts. Other data types, such as numbers, strings, dates, and bits can also be sorted.

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

Example - Sorting Results in Ascending Order

To sort your results in ascending order, you can specify the ASC attribute. If no value (ASC or DESC) is provided after a field in the ORDER BY clause, the sort order will default to ascending order. Let's explore this further.

In this example, we have a table called customers with the following data:

customer_idlast_namefirst_namefavorite_website4000JacksonJoetechonthenet.com5000SmithJanedigminecraft.com6000FergusonSamanthabigactivities.com7000ReynoldsAllencheckyourmath.com8000AndersonPaigeNULL9000JohnsonDerektechonthenet.com

Enter the following SQL statement:

Try It

SELECT *
FROM customers
ORDER BY last_name;

There will be 6 records selected. These are the results that you should see:

customer_idlast_namefirst_namefavorite_website8000AndersonPaigeNULL6000FergusonSamanthabigactivities.com4000JacksonJoetechonthenet.com9000JohnsonDerektechonthenet.com7000ReynoldsAllencheckyourmath.com5000SmithJanedigminecraft.com

This example would return all records from the customers sorted by the last_name field in ascending order and would be equivalent to the following SQL ORDER BY clause:

Try It

SELECT *
FROM customers
ORDER BY last_name ASC;

Most programmers omit the ASC attribute if sorting in ascending order.

Example - Sorting Results in descending order

When sorting your result set in descending order, you use the DESC attribute in your ORDER BY clause. Let's take a closer look.

In this example, we have a table called suppliers with the following data:

supplier_idsupplier_namecitystate100MicrosoftRedmondWashington200GoogleMountain ViewCalifornia300OracleRedwood CityCalifornia400Kimberly-ClarkIrvingTexas500Tyson FoodsSpringdaleArkansas600SC JohnsonRacineWisconsin700Dole Food CompanyWestlake VillageCalifornia800Flowers FoodsThomasvilleGeorgia900Electronic ArtsRedwood CityCalifornia

Enter the following SQL statement:

Try It

SELECT *
FROM suppliers
WHERE supplier_id > 400
ORDER BY supplier_id DESC;

There will be 5 records selected. These are the results that you should see:

supplier_idsupplier_namecitystate900Electronic ArtsRedwood CityCalifornia800Flowers FoodsThomasvilleGeorgia700Dole Food CompanyWestlake VillageCalifornia600SC JohnsonRacineWisconsin500Tyson FoodsSpringdaleArkansas

This example would sort the result set by the supplier_id field in descending order.

Example - Sorting Results by relative position

You can also use the SQL ORDER BY clause to sort by relative position in the result set, where the first field in the result set is 1, the second field is 2, the third field is 3, and so on.

In this example, we have a table called products with the following data:

product_idproduct_namecategory_id1Pear502Banana503Orange504Apple505Bread756Sliced Ham257KleenexNULL

Now enter the following SQL statement:

Try It

SELECT product_id, product_name
FROM products
WHERE product_name <> 'Bread'
ORDER BY 1 DESC;

There will be 6 records selected. These are the results that you should see:

product_idproduct_name7Kleenex6Sliced Ham4Apple3Orange2Banana1Pear

This example would sort the results by the product_id field in descending order, since the product_id field is in position #1 in the result set and would be equivalent to the following SQL ORDER BY clause:

Try It

SELECT product_id, product_name
FROM products
WHERE product_name <> 'Bread'
ORDER BY product_id DESC;

Example - Using both ASC and DESC attributes

When sorting your result set using the SQL ORDER BY clause, you can use the ASC and DESC attributes in a single SELECT statement.

In this example, let's use the same products table as the previous example:

product_idproduct_namecategory_id1Pear502Banana503Orange504Apple505Bread756Sliced Ham257KleenexNULL

Now enter the following SQL statement:

Try It

SELECT *
FROM products
WHERE product_id <> 7
ORDER BY category_id DESC, product_name ASC;

There will be 6 records selected. These are the results that you should see:

product_idproduct_namecategory_id5Bread754Apple502Banana503Orange501Pear506Sliced Ham25

This example would return the records sorted by the category_id field in descending order, with a secondary sort by product_name in ascending order.

Which command is used to reverse the ORDER BY results?

Which command can show results in alphabetical order?

Is reverse alphabetical order ascending or descending?

Which is the correct syntax for ORDER BY keyword?