Hello,
I have the following query/result:
auction=# select max(b.lot) as quantity,max(b.price) as price,p.login from bid b,
person p where b.auction_id = 84 and p.id = b.person_id group by p.login order by
max(price);
quantity | price | login
--+---+---
1 | 5
Could it be that the first query is using max which will only return the max
for b.lot and b.price..
so maybe.
select sum(max(b.lot))as quantity, max(b.price) as price from bid b, person
p where b.auction_id = 84 and p.id = b.person_id ;
Just a thought.
-Original Message-
From: [EMAIL
Louis-David Mitterrand <[EMAIL PROTECTED]> writes:
> Now I would like to sum() all results from the quantity column and
> return it with one SELECT statement. Is that possible?
You can't have aggregates of aggregates in a single SELECT structure;
that's just not in the SQL execution model. The w
It appears that postgres 7.0 does not support repeatable read for
transaction isolation. Is this planned? If so, when?
-Michael
_
http://fastmail.ca/ - Fast Free Web Email for Canadians
Tom,
> You can't have aggregates of aggregates in a single SELECT structure;
> that's just not in the SQL execution model. The way around this is
> to write multiple levels of SELECT, using either selection from a
> grouped/aggregated view or subselect-in-FROM. Unfortunately Postgres
> doesn't
Well, it's not a single SELECT, but why not use something like:
SELECT MAX(b.lot) AS quanity, max(p.price) AS price, p.login
INTO TEMPORARY TABLE temp1
FROM bid b, person p
WHERE b.auction_id=84 AND p.id=b.person_id
GROUP BY p.login
ORDER BY max(price);
SELECT SUM(quanity) from temp1;
If you n
"Michael Richards" <[EMAIL PROTECTED]> writes:
> It appears that postgres 7.0 does not support repeatable read for
> transaction isolation. Is this planned? If so, when?
?? Maybe you need to do SET TRANSACTION ISOLATION LEVEL?
regards, tom lane
Mr. Covell,
> 2. When I try to update "routes" table, it updates.
Actually, what I'm curious about is this part. Most databases that
support foriegn keys will not allow you to modify them as long as a
relation exists referencing the key, on either the master or child side,
unless you are updati
On Tue, Sep 19, 2000 at 01:17:01PM -0500, John McKown wrote:
> Well, it's not a single SELECT, but why not use something like:
>
> SELECT MAX(b.lot) AS quanity, max(p.price) AS price, p.login
> INTO TEMPORARY TABLE temp1
> FROM bid b, person p
> WHERE b.auction_id=84 AND p.id=b.person_id
> GROUP
On Tue, Sep 19, 2000 at 11:06:06AM -0700, Josh Berkus wrote:
>
> Unfortunately, Louis-David, I don't see any way around subselects in the
> FROM clause as Tom mentions, which are not currently supported. I'd
> suggest using a Function to create a temporary table or view and
> summarizing from th
DB: Postgresql 7.0.2
OS: Solaris 2.6
Schema:
hosts table with fqhn column
routes table with fqhn foreign key hosts(fqhn)
Problem:
1. When I try to change fqhn in hosts, it complains that
I have now violated entry in "routes" table.
2. When I try to update "routes" table, it updates.
On Tue, 19 Sep 2000, Timothy Covell wrote:
> Schema:
> hosts table with fqhn column
> routes table with fqhn foreign key hosts(fqhn)
>
> Problem:
>
> 1. When I try to change fqhn in hosts, it complains that
> I have now violated entry in "routes" table.
>
> 2. When I try to update "rou
Hi, Timothy,
Try:
1. dump out the data of routes and drop it,
2. re-build your routes table,
CREATE TABLE routes (
field1 type1,
fqhn stype2,
CONSTRAINT if_host_exists
FOREIGN KEY(fqhn) REFERENCES hosts
ON UPDATE CASCADE ON DELETE CASCADE
);
or use alter table add constraint
13 matches
Mail list logo
