Обсуждение: MSAccess-like Last() with sorting before grouping
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
On Wed, May 14, 2003 at 17:50:04 -0700, Octavio Alvarez <alvarezp@octavio.ods.org> wrote: > > 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? If you are willing to use nonstandard sql, distinct on should do what you want. Something like: select distinct on (group_key) id, group_key, sort_key, data from table_name order by sort_key desc;
"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
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