Geek asks and answers

PostgreSQL ORDER BY with views

Let's say I want to write a simple query SELECTthat uses 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 is that it is p.last_nameno longer available when I go to complete the final ORDER BY.

How can I sort the final query so that the original presentation sequence peoplematches the final query?

A simple solution here is to simply include p.last_name with the view. I do not want to do this - my real example (much more complicated) makes the problem.

In the past, I have done similar things with temporary tables. For example, I create a table with CREATE TEMP TABLE testing WITH OIDS, and then execute ORDER BY testing.oidto go through the original sequence.

Is it possible to do the same with representations?

+5
3

, row_number() over().

:

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;

SQL Fiddle, .

@Erwin Brandstetter, rank(), ( , ).

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;

, row_number(), , - . rank(), , .

.

+3

@sgeddes, rank():

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

, .
→ sqlfiddle

+2

row_number 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
+1

More articles:


All Articles