Could you do something like this.
SELECT date_from, date_to from table_name
WHERE date_from = (select MAX(date_from) from table_name)
AND date_to =(select MAX(date_to) from table_name)
AND date_from IS NOT NULL;
However I don't need the Max(date_from) and max(date_to), what I want is the details from the last time an entry was inserted into the table.
This might be a much smaller date range.
As a bit of background, this table is populated from a PeopleSoft Panel, the date's are selected by the user to decide what information they wish to view, this could change frequently, only the most current date range is valid, although the table will hold all historical information.
There is no other field that I can use to uniquely identify which row is the most current (other than rowid).
I understand that this isn't the most efficient way of doing things, unfortunately I have no choice.
Originally posted by alison
SELECT OPRID, DATE_FROM, DATE_TO, ROWNUM
WHERE ROWID=(SELECT MAX(ROWID) FROM PS_CB_EXCEPT_CTL)
AND DATE_FROM IS NOT NULL;
I think you need to move your criteria into the inner select statement:
select OPRID, DATE_FROM, DATE_TO
where DATE_FROM IS NOT NULL );
When I ran the above query against your sample data, I got the result you wanted.
As a side note, are we sure that the MAX ROWID of the rows having DATE_FROM populated will always return the last row inserted into the table? What happens when rows get deleted from the table and that space is reused for an insert later?
If you need the data based on the order it was inserted, then you need a trigger-populated field called INSERTED_TS that tells you when each record was inserted, period.
*DO NOT* use ROWID in production SQL! ROWID is useful for certain utility-type functions (finding duplicates, etc.), but should *never* be in a regular SQL statement in your application.
However, even if you don't follow this rule, you should know that the ROWID is simply an internal pointer to the physical location of the row on disk and has *no direct correlation* with when the record was inserted. There is a casual correlation in that the ROWID will generally increase as rows are added to the table because rows are *usually* added to the end. *However*, this is not a rule, just a usual occurence. As soon as any updates or deletes are performed on this table, your assumptions about ROWID go out the window. Even normal inserts can mess up the ROWID order depending on splits, etc.
So again, if you need to know when the row was inserted, then you need a column to capture and store that information. ROWID will not give it to you.