SQL Transact - ORDER BY

SQL Transact – ORDER BY clause

There is a certain satisfaction in sorting tables into the order that you want. SQL transact uses the ORDER BY clause to make the data neater and easier to interpret.

Lesson 1 – Selecting your data then sorting with: ORDER BY.

Situation: Your boss wants a list of all customers in alphabetical order.

— SQL statement to put customers in alphabetical order

USE Northwind
SELECT companyname, contactname
FROM customers
ORDER BY ‘companyname’
GO

Learning Points

Note 1: When you wish to sort, ORDER is always followed with BY.

Note 2: SELECT is a pre-requisite for ORDER BY.

Lesson 2 – Sorting out those products

In this scenario, the boss wants a list of products and their prices, moreover, he wants the most expensive item at the top of your list.

— SQL statement to sort prices into descending order

USE Northwind

SELECT productname, unitprice

FROM products

ORDER BY unitprice

Learning Points

Note 1: The products are in sequence, however the order is the reverse of what the boss wants. What can we do?

Lesson 2 – Sorting out those products in DESC ORDER

— SQL statement to sort prices into descending order

USE Northwind

SELECT productname, unitprice

FROM products

ORDER BY unitprice DESC

Note 1: You can explicitly sort data with ASC or DESC depending on which value you want at the top.

Other conditions to try.

Guy’s Out Takes

If you like a challenge, troubleshoot this transact statement and see if you can correct the error.

SQL Transact Out Take 1

What is wrong here?

— SQL statement to sort prices into descending order

USE Northwind

SELECT productname, unitprice

FROM products

ORDER unitprice, DESC

Answer to a sneaky problem: There are two faults here, BY is missing as in ORDER By. Also there is an unwanted comma after unitprice,

It should be:

ORDER BY unitprice DESC not ORDER unitprice, DESC

SQL 2000 Transact – ORDER BY clause