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/ it WORKED under postgres user with sample 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 below is the control file , I can not find out why I can not run under other user ids. 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 <melvin6...@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 <robjsarg...@gmail.com> wrote: > >> On 09/02/2015 05:14 PM, 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 >> >> I saw some posting in wiki with a custom function (or C extention) >> to do this. Is it widely used and reliable? >> https://wiki.postgresql.org/wiki/First/last_(aggregate) >> >> I am wondering why these two functions are not part of postgresql built-in >> functions as it has many use cases >> >> Thanks >> >> If you're ordering by col1, does >> >> select min(col1), max(col1) from table order by col1 >> >> not 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. >
