A literal in the Projection clause

The following query uses a literal in the projection list to tag the output of part of a union so it can be distinguished later. The tag is given the label sortkey. The query uses sortkey to order the retrieved rows.

Figure 1. Query

SELECT '1' sortkey, lname, fname, company, 
     city, state, phone 
   FROM customer x
   WHERE state = 'CA'
UNION
SELECT '2' sortkey, lname, fname, company, 
     city, state, phone 
   FROM customer y
   WHERE state <> 'CA'
   INTO TEMP calcust;
SELECT * FROM calcust
   ORDER BY 1;

The query creates a list in which the customers from California appear first.

Figure 2. Query result

sortkey  1
lname    Baxter
fname    Dick
company  Blue Ribbon Sports
city     Oakland
state    CA
phone    415-655-0011

sortkey  1
lname    Beatty
fname    Lana
company  Sportstown
city     Menlo Park
state    CA
phone    415-356-9982
⋮
sortkey  2
lname    Wallack
fname    Jason
company  City Sports
city     Wilmington
state    DE
phone    302-366-7511