Re: Is this a hack?
From: Joe Celko (joe.celko_at_northface.edu)
Date: 02/19/04
- Next message: Joe Celko: "Re: Increasing performance of query using a bit field"
- Previous message: chris: "Is this a hack?"
- In reply to: DaveF: "Is this a hack?"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 18 Feb 2004 16:59:21 -0800
>> I need to return a result set with 4 fields [sic]: the name and price
of the highest priced 'a' product and the name and price of the highest
priced 'b' product. <<
Give up putting both answers in one row and you can do it easily.
CREATE TABLE Products
(p_name CHAR(3) NOT NULL PRIMARY KEY,
p_class CHAR(2) NOT NULL,
p_price DECIMAL(8,4) NOT NULL);
INSERT INTO Products VALUES ('P01', 'a', 1.00);
INSERT INTO Products VALUES ('P02', 'a', 2.00);
INSERT INTO Products VALUES ('P03', 'b', 1.00);
INSERT INTO Products VALUES ('P04', 'b', 2.00);
INSERT INTO Products VALUES ('P05', 'b', 3.00);
INSERT INTO Products VALUES ('P06', 'a', 4.00);
INSERT INTO Products VALUES ('P07', 'b', 100.00);
INSERT INTO Products VALUES ('P08', 'a', 2.00);
INSERT INTO Products VALUES ('P09', 'a', 2.00);
INSERT INTO Products VALUES ('P10', 'a', 200.00);
SELECT P1.p_class, P1.p_name, P1.p_price
FROM Products AS P1
WHERE P1.p_price
= (SELECT MAX(P2.p_price)
FROM Products AS P2
WHERE P1.p_class = P2.p_class);
Results
p_class p_name p_price
========================
a P10 200.0000
b P07 100.0000
Now, let's figure out what your conceptual mistake was. The result of a
query should be a table. A table does not have repeated attributes in
its rows; just as a table does not have repeated rows; just as a schema
does not have repeated tables. Ergo, your results were not a proper
table -- class and price were repeated!
Hey, you were trying to do formatting for display in the SELECT list.
That always costs you in complexity. This is why it is so important not
to call a column a field; it screws up your mental model.
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
- Next message: Joe Celko: "Re: Increasing performance of query using a bit field"
- Previous message: chris: "Is this a hack?"
- In reply to: DaveF: "Is this a hack?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
