The SQL ORDER BY clause

A short video covering the ORDER BY clause within a SQL SELECT statement.

For beginners. How to use the ORDER BY clause in the SELECT statement. Covers multiple sort keys, ascending, descending, how NULLs are handled, and best practice.

Here is the SQL that goes with the demo.

-- Database by Doug
-- Douglas Kline
-- 9/16/2015
-- all about ORDER BY

USE Northwind

-- refresher

-- the result of a SELECT statement is a table
-- it has columns and rows, defined by the SELECT statement


SELECT ProductName,
       ProductID, 
       supplierID
FROM   Products
WHERE  unitprice > 10

-- the SELECT statement above creates a two-column, many-row table

-- the SELECT *clause* defines the which columns are in the resulting table, and their order

-- the WHERE clause defines which rows are in the resulting table

-- refresher over...

-- but what about the the *order* of the rows?

-- is there a default ordering?

SELECT ProductName
FROM   Products

SELECT ProductName,
       ProductID
FROM   Products

-- looking at the results, it seems like they are ordered by productID
 -- let's try the above in different orders of operation?

 -- changes in ordering of the rows, even though the actual records included do not change

 -- in short, the database engine will return the records in the most convenient order (for itself)
 -- it will NOT spend any extra time ordering rows for YOUR convenience, unless you ask

 -- so here's how you do it - the ORDER BY clause

 SELECT   ProductName
 FROM     Products
 WHERE    unitprice > 10
 ORDER BY ProductName ASC

  -- the ORDER BY clause always appears at the end of the SELECT statement

 -- the query above orders by the productname in ascending order

 -- the ordering is alphabetical, because ProductName is not numeric - it is character-based

 SELECT   ProductName
 FROM     Products
 WHERE    unitprice > 10
 ORDER BY ProductName DESC

 -- orders descending

-- in these examples, productName is the sort key, used to order either ascending or descending

-- by default, the ordering of a sort key is ascending
 
 SELECT   ProductName
 FROM     Products
 WHERE    unitprice > 10
 ORDER BY ProductName 

 -- so the above and the below are equivalent

 SELECT   ProductName
 FROM     Products
 WHERE    unitprice > 10
 ORDER BY ProductName ASC


 -- another example, with numeric data

SELECT   ProductName,
         unitprice
FROM     Products
ORDER BY unitprice DESC

-- " show the products with the most expensive at the top"

-- notice that the sort key does not need to be the first column

-- furthermore, the sort key need not be in any other clause of the SELECT statement


SELECT   ProductName,
         unitprice
FROM     Products
WHERE    reorderlevel > 10
ORDER BY productID ASC

-- note that each clause (SELECT, WHERE, ORDER BY) has different fields in it

-- However, the above query can be diconcerting to users
-- it is more common to have the sort keys in the SELECT clause, and in the same order as in the SELECT clause

-- like this
SELECT   ProductID,
         ProductName,
         unitprice
FROM     Products
WHERE    reorderlevel > 10
ORDER BY productID ASC

-- now the ordering is clear to the user

-- it is also possible to use multiple sort keys

SELECT   SupplierID,
         ProductID,
         ProductName,
         unitprice
FROM     Products
ORDER BY SupplierID ASC,
         ProductID  ASC

-- this means...
-- sort first by the SupplierID, ascending
-- then, when multiple records have the same SupplierID, sort by ProductID, ascending

-- note also how NULLs are handled for numerics
-- NULLs are considered to be smaller than numeric values
SELECT   SupplierID,
         ProductID,
         ProductName,
         unitprice
FROM     Products
ORDER BY SupplierID DESC,
         ProductID  ASC

-- how about characters?

SELECT   QuantityPerUnit,
         ProductName
FROM     Products
ORDER BY QuantityPerUnit

-- again, NULLs are considered to be smaller than characters

-- for SQL embedded in systems, you should always include an order by
-- consumers of the SELECT results may assume an ordering and actually *depend* on it
-- furthermore, it is best to have a *unique* ordering
-- again, downstream systems may assume and *depend* on a unique ordering

-- a non-unique ordering:

SELECT    ProductName
FROM      Products
ORDER BY  ProductName

-- improvement to make the ordering unique:

SELECT    ProductName
FROM      Products
ORDER BY  ProductName,
          ProductID

-- all about ORDER BY, the end :-)

Simple WHERE clauses

A short video covering simple WHERE clauses within a SQL SELECT statement.

For beginners. Covers the comparison operators, and logical expressions using NOT, AND, and OR.

Here is the SQL that goes with the demo.

Use Northwind

SELECT 'FRED' AS [firstName],
       23     AS [age]

-- SELECT statements always returns a table

-- the SELECT clause determines the columns of the table

SELECT *
FROM   Products

-- so the * 'wildcard' means 'all columns'
-- the above statement says 'create a table with a column for every column in the Products table'

SELECT   ProductID
FROM     Products

-- in this next statement, I am re-ordering the columns
SELECT   ProductName,
         ProductID, 
         unitprice
FROM     Products

-- we are seeing all rows from the products table
-- note the ordering of the rows

-- to specify the order of the rows, we need a new clause: ORDER BY
SELECT   Productname,
         ProductID,
         unitprice
FROM     Products
ORDER BY ProductID ASC

-- or
SELECT   Productname,
         ProductID,
         Unitprice
FROM     Products
ORDER BY UnitPrice DESC


-- 

-- at this point, we can control:
--  which columns with the SELECT clause
--  the *order* of the columns with the SELECT clause
--  the source of the data with the FROM clause
--  the *order* of the rows

-- but we haven't yet specified *which* rows
-- we need the WHERE clause for that

SELECT   Productname,
         ProductID,
         Unitprice
FROM     Products
WHERE    unitprice < 8
ORDER BY UnitPrice DESC

-- so here's the general form of the SELECT statement so far:

--SELECT   
--FROM     
--WHERE    
--ORDER BY 

-- SELECT and ORDER BY take lists of expressions
-- whereas FROM and WHERE take expressions

-- here's what I mean
SELECT   LEFT(ProductName, 3),
         unitprice * 2.2
FROM     Products
ORDER BY ProductName,
         SQRT(unitprice) DESC

-- see how the items in SELECT and ORDER BY are lists - they have commas
-- and they can be *expressions*

SELECT   Productname,
         ProductID,
         Unitprice
FROM     Products
WHERE    unitprice < 8
ORDER BY UnitPrice DESC

-- this part is an expression: unitprice < 8
-- furthermore, it's a *logical* expression
-- it is either true or false (zero or one)

SELECT   unitprice,
         CONVERT(bit, CASE WHEN unitprice < 8 then 1 else 0 end) AS [cheap product]
FROM     Products
ORDER BY unitprice asc

-- don't pay attention to the CONVERT... expression
-- but notice, I can *test* whether or not a unitprice is less than 8

-- now the same thing, but only show the cheap products
SELECT   unitprice,
         CONVERT(bit, CASE WHEN unitprice < 8 then 1 else 0 end) AS [cheap product]
FROM     Products
WHERE    unitprice < 8
ORDER BY unitprice asc

-- what about other ways?

SELECT   ProductID,
         unitprice,
         ProductName,
         discontinued
FROM     Products
WHERE    unitprice >= 10
ORDER BY ProductID

-- comparison operators: >, >=, <, <=, =
-- and 'not equal to' can be <>  or !=

SELECT   ProductID,
         unitprice,
         ProductName,
         discontinued
FROM     Products
WHERE    ProductName = 'Chai'
ORDER BY ProductID

SELECT   ProductID,
         unitprice,
         ProductName,
         discontinued
FROM     Products
WHERE    ProductName > 'Chai'
ORDER BY ProductID

SELECT   ProductID,
         unitprice,
         ProductName,
         discontinued
FROM     Products
WHERE    ProductName <> 'Chai'
ORDER BY ProductID

SELECT   ProductID,
         unitprice,
         ProductName,
         discontinued
FROM     Products
WHERE    discontinued = 1
ORDER BY ProductID

-- with an embedded expression

SELECT   ProductID,
         unitprice,
         ProductName,
         discontinued
FROM     Products
WHERE    (Unitprice)/2.0 > 5
ORDER BY ProductID

-- (Unitprice)/2.0 is a mathematical expression that evaluates as a number
-- (Unitprice)/2.0 > 5 is a logical expression that evaluates as 'true' or 'false'

-- now for some logical operators: NOT, AND, OR
SELECT   ProductID,
         unitprice,
         ProductName,
         discontinued
FROM     Products
WHERE    discontinued = 1
ORDER BY ProductID

SELECT   ProductID,
         unitprice,
         ProductName,
         discontinued
FROM     Products
WHERE    NOT (discontinued = 1)
ORDER BY ProductID

-- combine two logical expression with AND
-- makes a bigger logical expression

SELECT   ProductID,
         unitprice,
         ProductName,
         discontinued
FROM     Products
WHERE    discontinued = 0
   AND   unitprice > 5
ORDER BY ProductID

SELECT   ProductID,
         unitprice,
         ProductName,
         discontinued
FROM     Products
WHERE    discontinued = 0
   OR    unitprice > 5
ORDER BY ProductID

-- and you can control the order of evaluation with parentheses
SELECT   ProductID,
         unitprice,
         ProductName,
         discontinued
FROM     Products
WHERE    NOT discontinued = 0 AND unitprice > 5
ORDER BY ProductID

-- which is the same as
SELECT   ProductID,
         unitprice,
         ProductName,
         discontinued
FROM     Products
WHERE    (NOT discontinued = 0) AND unitprice > 5
ORDER BY ProductID


-- which is different than 
SELECT   ProductID,
         unitprice,
         ProductName,
         discontinued
FROM     Products
WHERE    NOT (discontinued = 0 AND unitprice > 5)
ORDER BY ProductID


-- that's simple WHERE clauses

No-Table SELECT statements

A short video covering simple SELECT statements.

Very simple SQL SELECT statements without a table. No FROM clause!

For beginners. Covers renaming a column, selecting number and string literals, simple math expressions, string concatenation, and column ordering.

Here is the SQL that goes with the demo.

USE Northwind

-- basic no-table SQL statements

SELECT 54

-- look at the results
-- notice that this is a one-row, one-column table, with no name for the column

SELECT 'Hello'

-- similar to above, but with text characters
-- note that numbers are shown in black (if you are viewing in SQL Mgmt Studio)

SELECT '54' 

-- similar to first, but different, in that the result is NOT a number
-- note that character strings are shown in red

SELECT 54 AS [A Number]

-- here, we are assigning a name to the column
-- this is important, so that we can refer to the column if we want
-- note that "reserved words", are shown in blue

SELECT 2 + 2

-- here is a mathematical expression that is evaluated by the SQL engine on the remote server

SELECT 2 + 2 AS [The Number 4]


SELECT 2 * (17/3) AS [mathematical expression result]
SELECT 2.0 * (17.0/3.0) AS [mathematical expression result]

-- the math operators are similar to those in a spreadsheet
-- note that you can use parentheses to control the order of operations

SELECT 'Bar' + 'ney' AS [The name Barney]

-- here, we are using a character string expression
-- concatenating two character strings into a single character string

SELECT 'Barney' + ' ' + 'Rubble'  AS [A Flintstones character]

-- three strings concatenated to produce first and last names with a space between

SELECT  SQRT(34) AS [Square Root of 34]

-- you can use functions in an expression
-- the SQRT function calculates the square root of the number
-- functions show up as pink/magenta
-- functions have a name, then parentheses

SELECT   SQRT(34)                   AS [Square Root of 34],
         'Barney' + ' ' + 'Rubble'  AS [A Flintstones character]

-- you can generate more than one column in the table that is created by the SELECT
-- there's two columns defined here, with a comma between them

SELECT   SQRT(34)                   AS [Square Root of 34],
         'Barney' + ' ' + 'Rubble'  AS [A Flintstones character],
         2 * (17/3)                 AS [mathematical expression result]

-- here are three columns
-- note the comma at the end of each line

-- note also the formatting / spacing
-- you CAN write the above like this:

SELECT   SQRT(34) AS [Square Root of 34], 'Barney' + ' ' + 'Rubble'  AS [A Flintstones character], 2 * (17/3)  AS [mathematical expression result]

-- but it's harder to read

-- this is better

SELECT   SQRT(34) AS [Square Root of 34], 
'Barney' + ' ' + 'Rubble'  AS [A Flintstones character], 
2 * (17/3)  AS [mathematical expression result]

-- and even better

SELECT   SQRT(34) AS [Square Root of 34], 
         'Barney' + ' ' + 'Rubble'  AS [A Flintstones character], 
         2 * (17/3)  AS [mathematical expression result]

-- most readable is this

SELECT   SQRT(34)                   AS [Square Root of 34],
         'Barney' + ' ' + 'Rubble'  AS [A Flintstones character],
         2 * (17/3)                 AS [mathematical expression result]

Mongo and SQL

Here's a presentation that I'm giving to the Cape Fear Dot Net Developer Group, on July 22, 2015.