Re: Question on create view
dyip95@aol.com (Dyip95) wrote:
>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.
Your example and your two statements all seem to ask for different things.
However, I can suggest some possible options depending on what exactly you wish
to do.
If you wish to get the rows in a table added a certain length of time before
today try something like this:
CREATE VIEW date_view
AS SELECT order_num, order_date
FROM orders
WHERE order_date = today - n;
Where n represents the number of days back you wish to see.
If you wish just the last record added to the database:
CREATE VIEW last_view
AS SELECT order_num, order_date
FROM orders
WHERE systable.tabname = orders
AND order_num = n + systable.nrows - 1; ^ ^
| |
| - the starting number of your serial column
- the serial column in the table.
This second example requires you to update your statistics after every add
or delete and you must have a serial column in the table to check.
hope this helps,
Rob Zook