Re: MSAccess-like Last() with sorting before grouping

From: Octavio Alvarez <alvarezp(at)octavio(dot)ods(dot)org>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: MSAccess-like Last() with sorting before grouping
Date: 2003-05-18 10:09:22
Message-ID: 200305180309.22452.alvarezp@octavio.ods.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)octavio(dot)ods(dot)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(at)octavio(dot)ods(dot)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(at)postgresql(dot)org

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Dani Oderbolz 2003-05-18 11:59:39 Re: SELECT database meta-info?
Previous Message Matej Cepl 2003-05-17 02:55:16 Re: Problems with join (no such attribute, but it exists)