Re: MSAccess-like Last() with sorting before grouping - Mailing list pgsql-novice

I somehow managed to do it with standard SQL by using an INNER JOIN with it a
subselect of itself. It is kind of ugly as you said, and it is very slow on
large tables.

SELECT id, group_key, sort_key, data FROM table INNER JOIN (SELECT group_key
as gk, max(sort_key) as sk FROM table ORDER BY group_key) as table2 ON
group_key = gk AND sort_key = sk;

Now... say that you want the SECOND LAST (one before the max value for
sort_key)... How would you do it?

On Friday 16 May 2003 12:59, Jeff Eckermann wrote:
> "Last" is very non-portable, as you have found out.
> You can do this fairly easily in PostgreSQL with
> another non-portable syntax, SELECT DISTINCT ON ...
>
> In this case, you want:
>
> SELECT DISTINCT ON (group_key) id, group_key,
> sort_key, data
> FROM table
> ORDER BY sort_key DESC;
>
> You can also do this with subselects, but it's pretty
> ugly...
>
> --- Octavio Alvarez <alvarezp@octavio.ods.org> wrote:
> > Hello! This is my first post to the list. I hope I'm
> > not just blanked out,
> > and asking a question with a simple answer. ;-)
> >
> > I need a query to return the last value of a set of
> > grouped-by records
> > when a column is sorted. For example:
> >
> > TABLE: (
> >   id         integer UNIQUE,
> >   group_key  integer,
> >   sort_key   integer,
> >   data       integer
> > )
> >
> > Say it has the following rows:
> >
> >  id | group_key | sort_key | data
> > ----+-----------+----------+------
> >   1 |         1 |        1 |    1
> >   2 |         1 |        2 |    6
> >   3 |         1 |        5 |    6
> >   4 |         1 |        9 |    2
> >   5 |         2 |        3 |    1
> >   6 |         2 |        4 |    3
> >   7 |         2 |        7 |    3
> >   8 |         3 |        6 |    5
> >   9 |         3 |        3 |    4
> > (9 rows)
> >
> > I need it to have the following output:
> >
> >  id | group_key | sort_key | data
> > ----+-----------+----------+------
> >   4 |         1 |        9 |    2
> >   7 |         2 |        7 |    3
> >   8 |         3 |        6 |    5
> >
> > Which is, selecting the top value from sort_key from
> > each different set of
> > group_key. I can't program a last() function because
> > it wouldn't work for
> > group_key=3 in the example. ORDER BY always sorts
> > the results after the
> > grouping. I tried min/max functions but they apply
> > to each column
> > individually.
> >
> > Is there any way I can ask for this info to the SQL
> > server?
> >
> > Thank you very much.
> >
> > --
> > Octavio Alvarez Piza.
> > E-mail: alvarezp@octavio.ods.org
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
> __________________________________
> Do you Yahoo!?
> The New Yahoo! Search - Faster. Easier. Bingo.
> http://search.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org