SQL Query - A better way?

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 EVENTNAME='AssignedChanged'
AND LASTUPDATE = ( SELECT MAX(LASTUPDATE) FROM ACTION_HISTORY WHERE
CASE_ID = '534623' AND EVENTNAME='AssignedChanged')
ORDER BY LASTUPDATE DESC;



Celko Answers
>> 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
REFERENCES Inventory(upc),
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),
etc.);


You then use a BETWEEN predicate to get the appropriate price.


SELECT ..
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 (..)
AS
SELECT ..
FROM PriceHistory
WHERE end_date IS NULL;