Re: Question on create view
: rob@dssmktg.com (Robert Minter) writes:
: * > Can I create a view on a column which is define as either date or
: * datetime
: * > and retrieving only the latest row of data ?
: * >
: * > Example:
: * > 100 10/25/93
: * > 100 02/05/95
: * > 200 08/09/96
: * >
: * > I want my view to display only the second and third row.
: * >
: *
: * Dominic,
: *
: * Yes... I think :-)
: *
: * create view view_name as
: * select col1, max(date_col) from table_name;
: *
: * Check out the "Informix Guide to SQL - Syntax" manual for more details. =
: * It
: * doesn't say you CAN'T do this !!
:
: I've tested this and it works fine. You will need to do it as such:
:
: CREATE VIEW VIEW_NAME(col1, dt_col) AS
: SELECT col1, MAX(date_col) FROM table_name
: GROUP BY col1;
Yes, this will give the two last rows in the above example, but are you
shure it gives the results the original questioner wanted?
Perhaps it should have been like this:
CREATE VIEW VIEW_NAME(col1, dt_col) AS
SELECT col1, date_col FROM table_name
WHERE date_col > (SELECT min(date_col) from table_name);
I am not shure what is meant by "only the latest row of data", but some
variant of the above may the right answer.
I haven't tested this as a view, but it ought to work.
Nils.Myklebust@ccmail.telemax.no
NM-data, Aasesvei 71, 1300 Sandvika, Norway
My opinions are those of my company