Finding max/min pairs in PostgreSQL

Question

I have two columns of x,y coordinates in a table on a Postgres database.

I can find the extremes in x and y using:

select min(x), max(x), min(y), max(y)
from coords

How do assign these to variables something like this:

select min(x) as xmin, max(x) as xmax, min(y) as ymin, max(y) as ymax
from coords

to achieve

select y
from coords
where x = xmin

etc...to obtain the four extreme points in the half-billion row dataset? (the point of the exercise)

the desired select statement works but I can't use the "where" clause as it says that xmin is not a column. What is the correct approach, or in the unlikely event that I am using the correct approach, what is the correct syntax?

Gordon Linoff · Accepted Answer · 2013-01-23 14:12:43Z

3

One way is with a join and subquery:

select c.*
from coords c join
     (select min(x) as xmin, max(x) as xmax, min(y) as ymin, max(y) as ymax
      from coords
     ) clim
     on c.x in (clim.xmin, clim.xmax) or c.y in (clim.ymin, clim.ymax)

Another way to do this is with window functions:

select c.*
from (select c.*,
             least(row_number() over (order by x),
                   row_number() over (order by x desc),
                   row_number() over (order by y),
                   row_number() over (order by y desc)
                  ) as seqnum
      from coords c
     ) c
where seqnum = 1

And another way, if you really want just 4 points is:

select * from coords order by x limit 1 union all
select * from coords order by x desc limit 1 union all
select * from coords order by y limit 1 union all
select * from coords order by y desc limit 1

With such a large number of rows, these will all run faster if you have indexes on x and y. In this case, the last is probably the fastest.

answered Jan 23, 2013 at 14:12

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

DHBI Over a year ago

Lastest is fastest - 600-640 ms per statement.

Collectives™ on Stack Overflow

Finding max/min pairs in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related