Create a view from table with dynamic where clause

Question

I have a table with a column called YEAR. I would like to create a view from this table, with YEAR always equal to the one with max value. Whenever new records with YEAR values greater than the current max one are inserted, the view would automatically be reflected accordingly. Is this possible and if so how can I accomplish that.

Answer 1

Yes. There might be better ways depending upon your RDBMS but here's one way.

CREATE VIEW YourView
AS
SELECT Col1, Col2,Year
FROM YourTable
WHERE Year=(SELECT MAX(Year) FROM YourTable)