Обсуждение: get first and last row in one sql as two columns
Hi:
I need to get the first and last tow in one sql like belowhttps://wiki.postgresql.org/wiki/First/last_(aggregate)
On 09/02/2015 05:14 PM, Tom Smith wrote:
If you're ordering by col1, doesThanksfunctions as it has many use casesI am wondering why these two functions are not part of postgresql built-into do this. Is it widely used and reliable?I saw some posting in wiki with a custom function (or C extention)select first(col1), last(col1) from table order by col1Hi:I need to get the first and last tow in one sql like below
https://wiki.postgresql.org/wiki/First/last_(aggregate)
select min(col1), max(col1) from table order by col1not do the trick;
On 09/02/2015 05:14 PM, Tom Smith wrote:
But what I think you are looking for are the windowing functions as what you propose breaks down pretty quickly with more complicated queries.Thanksfunctions as it has many use casesI am wondering why these two functions are not part of postgresql built-into do this. Is it widely used and reliable?I saw some posting in wiki with a custom function (or C extention)select first(col1), last(col1) from table order by col1Hi:I need to get the first and last tow in one sql like below
https://wiki.postgresql.org/wiki/First/last_(aggregate)
Try this:
SELECT
(SELECT <your_column>
FROM <your_table>
ORDER BY <your_column> offset 0 LIMIT 1) ,
(SELECT <your_column>
FROM <your_table>
ORDER BY <your_column> OFFSET (SELECT COUNT(*) ) LIMIT 1)
FROM <your_table> LIMIT 1;
SELECT
(SELECT <your_column>
FROM <your_table>
ORDER BY <your_column> offset 0 LIMIT 1) ,
(SELECT <your_column>
FROM <your_table>
ORDER BY <your_column> OFFSET (SELECT COUNT(*) ) LIMIT 1)
FROM <your_table> LIMIT 1;
On Wed, Sep 2, 2015 at 7:27 PM, Rob Sargent <robjsargent@gmail.com> wrote:
If you're ordering by col1, doesOn 09/02/2015 05:14 PM, Tom Smith wrote:Thanksfunctions as it has many use casesI am wondering why these two functions are not part of postgresql built-into do this. Is it widely used and reliable?I saw some posting in wiki with a custom function (or C extention)select first(col1), last(col1) from table order by col1Hi:I need to get the first and last tow in one sql like below
https://wiki.postgresql.org/wiki/First/last_(aggregate)select min(col1), max(col1) from table order by col1not do the trick;
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
Thanks for suggestion. I could use two separate sqls, but I am looking to get it in a single sql.
I just compiled and installed (under postgres id) first_last_agg C extension.http://pgxn.org/dist/first_last_agg/
select first(column1), last(column1) from (values (null),(1),(3),(null)) as x
but somehow when I run under other users, it say
ERROR: function first(integer) does not exist
Any help would be appreciated.
# first_last_agg extension
comment = 'first() and last() aggregate functions'
default_version = '0.1.4'
module_pathname = '$libdir/first_last_agg'
relocatable = false
schema = pg_catalog
superuser = false
On Wed, Sep 2, 2015 at 7:47 PM, Melvin Davidson <melvin6925@gmail.com> wrote:
Try this:
SELECT
(SELECT <your_column>
FROM <your_table>
ORDER BY <your_column> offset 0 LIMIT 1) ,
(SELECT <your_column>
FROM <your_table>
ORDER BY <your_column> OFFSET (SELECT COUNT(*) ) LIMIT 1)
FROM <your_table> LIMIT 1;--On Wed, Sep 2, 2015 at 7:27 PM, Rob Sargent <robjsargent@gmail.com> wrote:If you're ordering by col1, doesOn 09/02/2015 05:14 PM, Tom Smith wrote:Thanksfunctions as it has many use casesI am wondering why these two functions are not part of postgresql built-into do this. Is it widely used and reliable?I saw some posting in wiki with a custom function (or C extention)select first(col1), last(col1) from table order by col1Hi:I need to get the first and last tow in one sql like below
https://wiki.postgresql.org/wiki/First/last_(aggregate)select min(col1), max(col1) from table order by col1not do the trick;Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
On Wed, Sep 02, 2015 at 07:14:40PM -0400, Tom Smith wrote: > Hi: > > I need to get the first and last tow in one sql like below > > select first(col1), last(col1) from table order by col1 Have you tried the window functions [1] last_value and first_value? You could use something like: SELECT first_value(col1) over (order by col1), last_value(col1) over (order by col1) FROM table; It should be as simple as that, or I miss your point. [1] http://www.postgresql.org/docs/current/static/functions-window.html []s -- Dickson S. Guedes @guediz - http://github.com/guedes
Вложения
Hi:
The window function works for me (with adding limit 1 in the end to output only one row needed instead of many duplicate rows).
thanks very much.
On Thu, Sep 3, 2015 at 6:51 AM, Dickson S. Guedes <listas@guedesoft.net> wrote:
On Wed, Sep 02, 2015 at 07:14:40PM -0400, Tom Smith wrote:
> Hi:
>
> I need to get the first and last tow in one sql like below
>
> select first(col1), last(col1) from table order by col1
Have you tried the window functions [1] last_value and first_value?
You could use something like:
SELECT first_value(col1) over (order by col1),
last_value(col1) over (order by col1)
FROM table;
It should be as simple as that, or I miss your point.
[1] http://www.postgresql.org/docs/current/static/functions-window.html
[]s
--
Dickson S. Guedes
@guediz - http://github.com/guedes
Tom Smith schrieb am 03.09.2015 um 14:11: >> >> SELECT first_value(col1) over (order by col1), >> last_value(col1) over (order by col1) >> FROM table; >> > The window function works for me (with adding limit 1 in the end to output only one row > needed instead of many duplicate rows). If that works for you with a LIMIT 1, then I don't understand why select min(col1), max(col1) from table doesn't work for you