Blog Archive for November 5, 2012

SQL Least and Greatest

November 5, 2012

The least and greatest functions work in a similar way to coalesce, in that you can pass in any number of parameters, and the function will return the highest of them all. Parameters can be static values or columns.

SELECT LEAST(44,777,33,22,11)

returns the following on ...

SQL With Clause

November 5, 2012

Using the SQL with clause you can effectively define a temporary table and then select from it. This is formally known as subquery factoring.

WITH band_names AS
  (SELECT b.name, r.country_code
  FROM bands_band b  
  INNER JOIN regions_region r ON r.id = b.region_id)
SELECT * from band_names WHERE country_code = 'no ...

SQL Coalesce Statement

November 5, 2012

Coalesce returns the first non null parameter. You can specify both columns and static strings as parameters, and you can specify as many parameters as you need to.

SELECT b.name, COALESCE(r.country_code, 'unknown')
FROM bands_band b
INNER JOIN regions_region r ON r.id = b.region_id

In the results ...

SQL Case Statement

November 5, 2012

In SQL, the CASE statement can be used to further control the output of a column, or limit the choices that are returned.

Due to the particular case I'm dealing with, this query will return a list of band names and their regions. Regions are the ISO country code ...