join problem or maybe group :(

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);

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

"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

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

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)---------------------------

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