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., Jan 31 '13 at 23:21
i doubt there will be an answer given your constraint. but good luck nonetheless. — amphibient, Jan 31 '13 at 23:22
@foampile It's possible with temp tables--I'm hoping it's possible with views as well :) — Elliot B., Jan 31 '13 at 23:23
Please note that allowing `ORDER BY` clauses in views appears to be a PostgreSQL extension - the standard doesn't allow for it (and none of the other major RDBMSs have it). — Clockwork-Muse, Jan 31 '13 at 23:28
@ElliotB. Please take a look at the generated query plan. Views in postgres work different from what you expect; they are actually merged into the *outside* plan, *as if* it were a `join (select p.person_id ,p.full_name ,p.phone FROM person p) AS p`, which obviously does not have the p.last_name either. — wildplasser, Jan 31 '13 at 23:31
@wildplasser Yeah, my original thought was that I might piggyback along a sequence column (with is effectively what `OIDS` does). I'm trying out some of the answers below--they look pretty promising. — Elliot B., Jan 31 '13 at 23:33
I'm always surprised that Postgres does allow an `order by` in a view. It simply doesn't make sense. — , Jan 31 '13 at 23:33
BTW: it is no show stopper to select more columns in the subquery/view than are actually used by the outer query, the optimiser will reduce the sub-plan to the bare minimum. I repeat: check the plan (the rowsize, in this case) — wildplasser, Jan 31 '13 at 23:36
@a_horse_with_no_name: the `order by` is silently ignored in a subplan. (IIRC, YMMV, etc) — wildplasser, Jan 31 '13 at 23:36
@wildplasser: if it is ignored it shouldn't be allowed in the first place, shouldn't it? (the "silently ignore" thing is more MySQL's expertise...) — , Jan 31 '13 at 23:38
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, Jan 31 '13 at 23:42
@wildplasser I always thought it would be ignored, but sgeddes answer relies on that and it works. Would it work for a large result set? — Clodoaldo Neto, Jan 31 '13 at 23:42
Just look at the generated plan. In my experience, it will be silently ignored iff it is used in a subquery. Maybe I'll need to do some research tomorrow. — wildplasser, Jan 31 '13 at 23:47
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, Jan 31 '13 at 23:51
@ErwinBrandstetter: good catch! I missed it. (I never use TEMPs, that's why ...) — wildplasser, Jan 31 '13 at 23:55

sgeddes · Accepted Answer · 2013-02-01T00:18:06.647

6

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 01 '13 at 00:18

answered Jan 31 '13 at 23:29

sgeddes

62,311
6
61
83

2

@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. – Erwin Brandstetter Jan 31 '13 at 23:59
@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 Feb 01 '13 at 00:12
@ErwinBrandstetter -- Many thanks -- see edits above! Wasn't even thinking about sorting on other parameters. Great catch. – sgeddes Feb 01 '13 at 00:18
Apart from that, I like your idea. +1. – Erwin Brandstetter Feb 01 '13 at 00:28

Erwin Brandstetter · Answer 2 · 2022-04-16T18:44:00.797

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 '22 at 18:44

answered Feb 01 '13 at 00:25

Erwin Brandstetter

605,456
145
1,078
1,228

score 1 · Answer 3 · edited Jan 31 '13 at 23:53

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 '13 at 23:53

Erwin Brandstetter

605,456
145
1,078
1,228

answered Jan 31 '13 at 23:29

Clodoaldo Neto

118,695
26
233
260

No need to add row_number() to the view. You can use it as I did below. Either way, nice answer! – sgeddes Jan 31 '13 at 23:29
Same problem here. Should be `rank()`. – Erwin Brandstetter Feb 01 '13 at 00:30

PostgreSQL ORDER BY with VIEWs

3 Answers3