PostgreSQL ORDER BY with VIEWs

Question

Let's say I want to write a simple SELECT query that uses a VIEW:

CREATE TEMP VIEW people AS
SELECT 
     p.person_id
    ,p.full_name
    ,p.phone
FROM person p
ORDER BY p.last_name;

SELECT
     p.*
    ,h.address
    ,h.appraisal
FROM people p
LEFT JOIN homes h
     ON h.person_id = p.person_id
ORDER BY p.last_name, h.appraisal;

The obvious problem here is that p.last_name is no longer available when I go to perform the final ORDER BY.

How can I sort the final query so that the original sequence of the people view follows through to the final query?

The simple solution here, is to just include p.last_name with the view. I don't want to do that - my real world example (much more complicated) makes that a problem.

I've done similar things with temp tables in the past. For example, I create the table with CREATE TEMP TABLE testing WITH OIDS and then do an ORDER BY testing.oid to pass through the original sequence.

Is it possible to do the same with views?

Answer 1

This is possible if you use row_number() over().

Here is an example:

SELECT
    p.*
    ,h.address
    ,h.appraisal
FROM (SELECT *, row_number() over() rn FROM people) p
LEFT JOIN homes h
    ON h.person_id = p.person_id
ORDER BY p.rn, h.appraisal;

And here is the SQL Fiddle you can test with.

As @Erwin Brandstetter correctly points out, using rank() will produce the correct results and allow for sorting on additional fields (in this case, appraisal).

SELECT
    p.*
    ,h.address
    ,h.appraisal
FROM (SELECT *, rank() over() rn FROM people) p
LEFT JOIN homes h
    ON h.person_id = p.person_id
ORDER BY p.rn, h.appraisal;

Think about it this way, using row_number(), it will always sort by that field only, regardless of any other sorting parameters. By using rank() where ties are the same, other fields can easily be search upon.

Good luck.

Answer 2

Building on the idea of @sgeddes, but use rank() instead:

SELECT p.*
     , h.address
     , h.appraisal
FROM  (SELECT *, rank() OVER () AS rnk FROM people) p
LEFT   JOIN homes h ON h.person_id = p.person_id
ORDER  BY p.rnk, h.appraisal;

db<>fiddle here - demonstrating the difference
_{Old sqlfiddle}

Answer 3

Create a row_number column and use it in the select.

CREATE TEMP VIEW people AS
SELECT 
     row_number() over(order by p.last_name) as i
    ,p.person_id
    ,p.full_name
    ,p.phone
FROM person p

SELECT
     p.*
    ,h.address
    ,h.appraisal
FROM people p
LEFT JOIN homes h
     ON h.person_id = p.person_id
ORDER BY p.i, h.appraisal