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