From: | Tom Smith <tomsmith1989sk(at)gmail(dot)com> |
---|---|
To: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
Cc: | Adam Brusselback <adambrusselback(at)gmail(dot)com>, PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: first_value/last_value |
Date: | 2016-05-19 03:04:50 |
Message-ID: | CAKwSVFHRgdrWge678ZmoxbQUU7dE-u7Bo8mYU59s-JPZM-w5zQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 <melvin6925(at)gmail(dot)com>
wrote:
>
>
> On Wed, May 18, 2016 at 10:36 PM, Adam Brusselback <
> adambrusselback(at)gmail(dot)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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Shrikant Bhende | 2016-05-19 07:18:47 | How to view creation date and time of a relation |
Previous Message | Melvin Davidson | 2016-05-19 02:42:50 | Re: first_value/last_value |