It would really save all the troubles for many people if postgresql has a built-in first/last function along with sum/avg. There is already a C extension and a wiki sample and implemented for window function. I am curious why these two functions were not added along their window implementation counter part, for completness and consistency
On Wed, May 18, 2016 at 10:42 PM, Melvin Davidson <melvin6...@gmail.com> wrote: > > > On Wed, May 18, 2016 at 10:36 PM, Adam Brusselback < > adambrusselb...@gmail.com> wrote: > >> Here is an example that works in a single query. Since you have two >> different orders you want the data back in, you need to use subqueries to >> get the proper data back, but it works, and is very fast. >> >> CREATE TEMPORARY TABLE foo AS >> SELECT generate_series as bar >> FROM generate_series(1, 1000000); >> >> CREATE INDEX idx_foo_bar ON foo (bar); >> >> >> SELECT * >> FROM ( >> SELECT bar >> FROM foo >> ORDER BY bar asc >> LIMIT 1 >> ) x >> UNION ALL >> SELECT * >> FROM ( >> SELECT bar >> FROM foo >> ORDER BY bar desc >> LIMIT 1 >> ) y; >> >> DROP TABLE foo; >> > > Seems to me SELECT min(<column>), max(<column>) FROM deja.vu ; would also > work. > > > -- > *Melvin Davidson* > I reserve the right to fantasize. Whether or not you > wish to share my fantasy is entirely up to you. >
