Quering a state in a set of states

From: Ja La <none_at_nowhere.dk>
Date: Sun, 25 Aug 2002 12:40:04 +0200
Message-ID: <3d68b387$0$43955$edfadb0f@dspool01.news.tele.dk>

Consider this situation:

Some entity ITEM has a time-varing state, ITEMSTATE, represented by to tables (ITEM -- 1-N -- ITEMSTATE)

ITEM( item_id, item_name), PK=(item_id)

ITEMSTATE( item_id, state_date, state_value), PK=(item_id, state_date); FK=(item_id) to ITEM

A query is wanted that gives the set of (item_name,state_value)-records for a given value of a parameter "date", such that the latest value is chosen; ie. state_date<=date and (at most) one record for each item_id.

How can such a (parametrized) query be formulated in SQL? Received on Sun Aug 25 2002 - 05:40:04 CDT