SQL Apprentice Question
I have a query that I have written and it works, but it seems a bit
redundant and I am wondering if there is a better way to write it.
My basic problem is I want to pull only 1 record, and that record is
the newest one based on the LASTUPDATE date field.
Here is my attempt at the query
SELECT * FROM ACTION_HISTORY
WHERE CASE_ID = '534623'
AND LASTUPDATE = ( SELECT MAX(LASTUPDATE) FROM ACTION_HISTORY WHERE
CASE_ID = '534623' AND EVENTNAME='AssignedChanged')
ORDER BY LASTUPDATE DESC;
>> My basic problem is I want to pull only 1 record [sic], and that record [sic] is the newest one based on the LASTUPDATE date field [sic]. <<
You are confusing rows and records, fields and columns, so yoiu wind up
with a data model that shows events and not facts. Each row should be
complete fact, and temporal facts have durations.
Let me use a history table for price changes. The fact to store is
that a price had a duration:
CREATE TABLE PriceHistory
(upc CHAR(13) NOT NULL
start_date DATE NOT NULL,
end_date DATE, -- null means current
CHECK(start_date < end_date), -- actaualloy needs more checks
PRIMARY KEY (upc, start_date),
item_price DECIMAL (12,4) NOT NULL
CHECK (item_price > 0.0000),
You then use a BETWEEN predicate to get the appropriate price.
FROM PriceHistory AS H, Orders AS O
WHERE O.sales_date BETWEEN H.start_date
AND COALESCE (end_date, CURRENT_TIMESTAMP);
It is also a good idea to have a VIEW with the current data:
CREATE VIEW CurrentPrices (..)
WHERE end_date IS NULL;