Обсуждение: join problem or maybe group :(
Hi All I want to select sections using left join on articles, but i want to retrive only one row from articles per section and the one should be the latest art_date. Can it be done ? CREATE TABLE sections ( sec_id SERIAL PRIMARY KEY, sec_name VARCHAR (30), sec_order INT2 ); CREATE TABLE articles ( art_id SERIAL PRIMARY KEY, sec_id INT4 REFERENCES sections ON UPDATE CASCADE ON DELETE CASCADE, art_name VARCHAR (30), art_date DATE ); Cheer
hmmm, me again. Ill attack the subject from another way. Can i limit the left joined table to one result ? like: select * from table1 left join table2 using (column) limit table2 1,0; ? > Hi All > > > I want to select sections using left join on articles, but i want to retrive > only one row from articles per section and the one should be the latest > art_date. > > Can it be done ? > > CREATE TABLE sections ( > sec_id SERIAL PRIMARY KEY, > sec_name VARCHAR (30), > sec_order INT2 > ); > > > CREATE TABLE articles ( > art_id SERIAL PRIMARY KEY, > sec_id INT4 REFERENCES sections ON UPDATE CASCADE ON DELETE CASCADE, > art_name VARCHAR (30), > art_date DATE > ); > > Cheer > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
On Thu, 29 Aug 2002, Ben-Nes Michael wrote: > hmmm, me again. > > Ill attack the subject from another way. > > Can i limit the left joined table to one result ? > > like: > > select * from table1 left join table2 using (column) limit table2 1,0; ? I don't think so, however, it's possible that maybe a distinct on in a subselect may help if you don't mind using postgresql extensions. Maybe something like (complete untested): select * from sections left join (select distinct on (sec_id) * from articles order by art_date desc) as articles using (sec_id); > > I want to select sections using left join on articles, but i want to > retrive > > only one row from articles per section and the one should be the latest > > art_date. > > > > Can it be done ? > > > > CREATE TABLE sections ( > > sec_id SERIAL PRIMARY KEY, > > sec_name VARCHAR (30), > > sec_order INT2 > > ); > > > > > > CREATE TABLE articles ( > > art_id SERIAL PRIMARY KEY, > > sec_id INT4 REFERENCES sections ON UPDATE CASCADE ON DELETE CASCADE, > > art_name VARCHAR (30), > > art_date DATE > > ); > > > > Cheer > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
found a way, but im not sure its the optimum way SELECT * from sections left join articles using (sec_id) where art_id = (SELECT art_id from articles where sec_id = sections.sec_id order by art_date DESC limit 1,0) order by sec_order; Just wonder, how can i do: select max(column1), column2 from table; its saying: ERROR: Attribute table.column2 must be GROUPed or used in an aggregate function and all i want is that column2 value will be the mate fo column1 max? like if clumn1 column2 1 a 2 b 3 c it will return 3,c > > On Thu, 29 Aug 2002, Ben-Nes Michael wrote: > > > hmmm, me again. > > > > Ill attack the subject from another way. > > > > Can i limit the left joined table to one result ? > > > > like: > > > > select * from table1 left join table2 using (column) limit table2 1,0; ? > > I don't think so, however, it's possible that maybe a distinct on in > a subselect may help if you don't mind using postgresql extensions. > > Maybe something like (complete untested): > > select * from sections left join > (select distinct on (sec_id) * from articles order by art_date desc) > as articles > using (sec_id); > > > > I want to select sections using left join on articles, but i want to > > retrive > > > only one row from articles per section and the one should be the latest > > > art_date. > > > > > > Can it be done ? > > > > > > CREATE TABLE sections ( > > > sec_id SERIAL PRIMARY KEY, > > > sec_name VARCHAR (30), > > > sec_order INT2 > > > ); > > > > > > > > > CREATE TABLE articles ( > > > art_id SERIAL PRIMARY KEY, > > > sec_id INT4 REFERENCES sections ON UPDATE CASCADE ON DELETE CASCADE, > > > art_name VARCHAR (30), > > > art_date DATE > > > ); > > > > > > Cheer > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 6: Have you searched our list archives? > > > > > > http://archives.postgresql.org > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
You syntax is not correct... All columns in the select must be in an aggregate function or in the group by clause. You could try something like select column1, column2 from table where column1 = ( select max( column1 ) from table ) -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Ben-Nes Michael Sent: Thursday, August 29, 2002 6:35 PM To: Stephan Szabo Cc: postgres Subject: Re: [GENERAL] join problem or maybe group :( found a way, but im not sure its the optimum way SELECT * from sections left join articles using (sec_id) where art_id = (SELECT art_id from articles where sec_id = sections.sec_id order by art_date DESC limit 1,0) order by sec_order; Just wonder, how can i do: select max(column1), column2 from table; its saying: ERROR: Attribute table.column2 must be GROUPed or used in an aggregate function and all i want is that column2 value will be the mate fo column1 max? like if clumn1 column2 1 a 2 b 3 c it will return 3,c > > On Thu, 29 Aug 2002, Ben-Nes Michael wrote: > > > hmmm, me again. > > > > Ill attack the subject from another way. > > > > Can i limit the left joined table to one result ? > > > > like: > > > > select * from table1 left join table2 using (column) limit table2 1,0; ? > > I don't think so, however, it's possible that maybe a distinct on in > a subselect may help if you don't mind using postgresql extensions. > > Maybe something like (complete untested): > > select * from sections left join > (select distinct on (sec_id) * from articles order by art_date desc) > as articles > using (sec_id); > > > > I want to select sections using left join on articles, but i want to > > retrive > > > only one row from articles per section and the one should be the latest > > > art_date. > > > > > > Can it be done ? > > > > > > CREATE TABLE sections ( > > > sec_id SERIAL PRIMARY KEY, > > > sec_name VARCHAR (30), > > > sec_order INT2 > > > ); > > > > > > > > > CREATE TABLE articles ( > > > art_id SERIAL PRIMARY KEY, > > > sec_id INT4 REFERENCES sections ON UPDATE CASCADE ON DELETE CASCADE, > > > art_name VARCHAR (30), > > > art_date DATE > > > ); > > > > > > Cheer > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 6: Have you searched our list archives? > > > > > > http://archives.postgresql.org > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
"Ben-Nes Michael" <miki@canaan.co.il> writes:
> Just wonder, how can i do:
> select max(column1), column2 from table;
> its saying:
> ERROR: Attribute table.column2 must be GROUPed or used in an aggregate
> function
> and all i want is that column2 value will be the mate fo column1 max?
You want DISTINCT ON ... see the weather-reports example on the SELECT
man page.
regards, tom lane
I think that a subselect will do that, but I'm not sure if it is the best way. Try something like: select max(column1), (select column2 from table where column1 = (select max(column1) from table)) from table; >Just wonder, how can i do: > >select max(column1), column2 from table; > >its saying: >ERROR: Attribute table.column2 must be GROUPed or used in an aggregate >function > >and all i want is that column2 value will be the mate fo column1 max? > >like if >clumn1 column2 >1 a >2 b >3 c > >it will return 3,c > -- Diogo de Oliveira Biazus diogo@ikono.com.br Ikono Sistemas e Automação http://www.ikono.com.br
There is a non-ansi way of doing it:
SELECT column1,column2 FROM table ORDER BY column1 DESC LIMIT 1;
- Stuart
> -----Original Message-----
> From: Diogo Biazus [mailto:diogo@ikono.com.br]
>
>
> I think that a subselect will do that, but I'm not sure if it is the
> best way.
> Try something like:
>
> select max(column1), (select column2 from table where column1
> = (select max(column1) from table)) from table;
>
>
> >Just wonder, how can i do:
> >
> >select max(column1), column2 from table;
> >
> >its saying:
> >ERROR: Attribute table.column2 must be GROUPed or used in
> an aggregate
> >function
> >
> >and all i want is that column2 value will be the mate fo column1 max?
> >
> >like if
> >clumn1 column2
> >1 a
> >2 b
> >3 c
> >
> >it will return 3,c
> >
>
> --
> Diogo de Oliveira Biazus
> diogo@ikono.com.br
> Ikono Sistemas e Automação
> http://www.ikono.com.br
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>