How to Rename the Columns Returned by the VALUES Statement in SQLite

If you’ve ever used the VALUES clause as a stand alone statement, you may have noticed that SQLite provides default column names for the results. SQLite conveniently names them column1, column2, and so on.

However as convenient as this is, you might want to provide names that are more meaningful.

Fortunately there’s an easy way to do that.

Example

Here’s an example of providing our own names for each column returned by the VALUES statement:

SELECT
  column1 AS id,
  column2 AS name
FROM (
  VALUES 
    (1, 'Fred'), 
    (2, 'Wilma'), 
    (3, 'Barney')
);

Result:

id  name  
--  ------
1   Fred  
2   Wilma 
3   Barney

Here, I placed the VALUES statement into a subquery. Then to rename the columns, I provided aliases for each column returned by the subquery.

So in this case I used column1 AS id to rename column1 to id, and column2 AS name to rename column2 to name.

Column Aliases in the ORDER BY Clause

We can refer to the column aliases when we use an ORDER BY against the outer query:

SELECT
  column1 AS id,
  column2 AS name
FROM (
  VALUES 
    (1, 'Fred'), 
    (2, 'Wilma'), 
    (3, 'Barney')
)
ORDER BY name;

Result:

id  name  
--  ------
3   Barney
1   Fred  
2   Wilma 

Or we can use the default name as given by SQLite:

SELECT
  column1 AS id,
  column2 AS name
FROM (
  VALUES 
    (1, 'Fred'), 
    (2, 'Wilma'), 
    (3, 'Barney')
)
ORDER BY column2;

Result:

id  name  
--  ------
3   Barney
1   Fred  
2   Wilma