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;

trpost (AT) gmail (DOT) com wrote:
I would do this as follows:

SELECT H.*
FROM action_history AS H
LEFT OUTER JOIN action_history AS H2 ON H.case_id = H2.case_id
AND H.eventname = H2.eventname AND H.lastupdate < H2.lastupdate
WHERE H.eventname = 'AssignedChanged' AND H.case_id = 534623
AND H2.case_id IS NULL;


Note that I took the quotes off of the case_id value. Assuming case_id
is an integer, it's better to compare it to an integer, instead of a string.

Regards,
Bill K.

trpost (AT) gmail (DOT) com wrote:
(which is db2's syntax) then it is much easier:

SELECT * FROM ACTION_HISTORY
WHERE CASE_ID = '534623'
AND EVENTNAME='AssignedChanged'
ORDER BY LASTUPDATE DESC
FETCH FIRST 1 ROW ONLY

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;

What is the database server you are using? Oracle/SQL Server/DB2?


trpost (AT) gmail (DOT) com wrote: