|
|
Posted by --CELKO-- on 10/28/06 01:40
>> I have a table with 4 relevant fields [sic] .. <<
Suggestions:
1) learn why a column is nothing like a field
2) Use a numeric code instead of a fake Boolean flag. That way the
MIN() will give you a contact in every location. I would bet you spend
time updating the flags.
CREATE TABLE Contacts
(state_code CHAR(2) NOT NULL,
city_name CHAR(25) NOT NULL,
contact_name CHAR(25) NOT NULL,
contact_priority INTEGER NOT NULL
CHECK (contact_priority > 0),
PRIMARY KEY (state_code, city_name, contact_name, contact_priority));
This will show one contact in every city
CREATE VIEW PrimaryContacts (state_code, city_name, contact_name)
AS
SELECT C1.state_code, C1.city_name, C1.contact_name
FROM Contacts AS C1
WHERE contact_priority
= (SELECT MIN (C2.contact_priority)
FROM Contacts AS C2
WHERE C1.state_code = C2.state_code
AND C1.city_name = C2.city_name);
Navigation:
[Reply to this message]
|