Using UNION to make a header

student_id   start_date 
123          2000 
234          2002

where 'student_id' and 'start_date' are literals selected from dual:

  SELECT  'student_id'
       ,  'start_date'
  FROM     dual
UNION
  SELECT  student_id
       ,  start_dt
  FROM    student_table
ORDER BY  1;

The result: 

123          2000 
234          2002
student_id   start_date 

So how do I write my statement so that 'student_id' and 'start_date' are on the top row?
Your two-part UNION is creating a two-part output

1. A one-line header
2. A multi-line body

You need to sort by two criteria:

1. The header line comes before the body lines
2. Within each section, lines are sorted by student_id (Since there's only one line in the header section, this will only really apply to the body.)

Now you need some way of telling SQL whether a line is in the header or body, so it can sort correctly. Here's one way:

  SELECT  'student_id'
       ,  'start_date'
       ,  1     -- Header will be sorted 1st
  FROM     dual
UNION
  SELECT  student_id
       ,  start_dt
       ,  2     --   Body will be sorted 2nd
  FROM    student_table
ORDER BY  3, 1;

This produces the following results:

student_id start_date          1
123        2000                2
234        2002                2

If you make an in-line view out of this result set, you will have the last column available for sorting without having to display it.

SELECT  student_id
     ,  start_dt
FROM
    (
      SELECT  'student_id'    AS student_id
           ,  'start_date'    AS start_dt
           ,  1               AS section_num
      FROM     dual
    UNION
      SELECT  student_id
           ,  start_dt
           ,  2               AS section_num
      FROM    student_table
    )
ORDER BY  section_num
       ,  student_id;

This is a simple example of a master-detail query. For a more complicated example using the same technique, see the last part of Rudy Limeback's column from May 14, 2003.