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?

My actual views and queries are much more complicated than the simplified granular example I've given above. It is needlessly complicated to explain why I want to avoid passing through the extra column. To answer this question, you'll have to accept that requirement without understanding why. — Elliot B.
– Elliot B., Commented Jan 31, 2013 at 23:21
I'm always surprised that Postgres does allow an order by in a view. It simply doesn't make sense. — user330315
– user330315, Commented Jan 31, 2013 at 23:33
LOL. Could be a bit sloppy or just eye candy. In relational terms, it would not make sense anyway: the resultset is unordered, so any ordering can be ignored. It won't harm (just like a LIMIT 1 inside an exists(...) correlated subquery ...) BTW: in particularly this case it does make sense: using the VIEW either in a outer query or in a subquery. The subquery may ignore; the outer query may not. — wildplasser
– wildplasser, Commented Jan 31, 2013 at 23:42
I took the liberty and fixed the illegal syntax for the view creation in the question, since it does not interfere with the question. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jan 31, 2013 at 23:51

sgeddes · Accepted Answer · 2013-02-01 00:18:06Z

7

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.

edited Feb 1, 2013 at 0:18

answered Jan 31, 2013 at 23:29

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Erwin Brandstetter Over a year ago

@ElliotB.: Actually, this answer is almost, but not quite correct. If there are identical peers for lastname in table person, row_number() assigns a distinct (effectively random) number and you get different results than you would when ordering by the original column lastname plus appraisal . Solution: use rank() instead.

sgeddes Over a year ago

@ElliotB: just change row_number above with rank. Think about it this way, row_number wont sort smith 1000 vs smith 100 - it uses the original row_number only. Great point, I'll edit my response.

sgeddes Over a year ago

@ErwinBrandstetter -- Many thanks -- see edits above! Wasn't even thinking about sorting on other parameters. Great catch.

Erwin Brandstetter Over a year ago

Apart from that, I like your idea. +1.

Erwin Brandstetter · Accepted Answer · 2022-04-16 18:44:00Z

4

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}

edited Apr 16, 2022 at 18:44

answered Feb 1, 2013 at 0:25

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Comments

Erwin Brandstetter · Accepted Answer · 2013-01-31 23:53:06Z

1

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

edited Jan 31, 2013 at 23:53

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

answered Jan 31, 2013 at 23:29

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

2 Comments

sgeddes Over a year ago

No need to add row_number() to the view. You can use it as I did below. Either way, nice answer!

Erwin Brandstetter Over a year ago

Same problem here. Should be rank().

Collectives™ on Stack Overflow

PostgreSQL ORDER BY with VIEWs

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related