Maximum ROWNUM Query

[alison]

Hi there,

I'm looking for some help on the following SQL query.

I have a table with the following data in it.

OPRID DATE_FROM DATE_TO
-------- --------- ---------
DOBELLA 01-JUL-01 31-AUG-01
MILLARL 01-JUN-00 31-DEC-01
TONNERA
TONNERA

I want to extract information from the date_from & date_to fields, but I only want to see the last values where it's not null.

i.e. I want 01-JUN-00 31-DEC-01

I tried using

SELECT OPRID, DATE_FROM, DATE_TO, ROWNUM
FROM PS_CB_EXCEPT_CTL
WHERE ROWID=(SELECT MAX(ROWID) FROM PS_CB_EXCEPT_CTL)
AND DATE_FROM IS NOT NULL;

This obviously give me nothing but I can't think of another way of doing it.

These values will be used in an SQR program.

I am running Oracle7.3.3.

Many thanks in Advance

Allie

[Mr.Hanky]

Hey Allison;
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;

Or am I just talking poo?
MH

[Edited by Mr.Hanky on 08-23-2001 at 08:59 AM]

[Mr.Hanky]

Actually now that I think about it if you use AND both conditions will need to be true, maybe OR would work better.
MH

[alison]

Thanks for your response MH

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.

Thanks

Alison

[Heath]

Originally posted by alison

SELECT OPRID, DATE_FROM, DATE_TO, ROWNUM
FROM PS_CB_EXCEPT_CTL
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:

Try


    select OPRID, DATE_FROM, DATE_TO
    from PS_CB_EXCEPT_CTL
    where ROWID=(
        select MAX(ROWID)
        from PS_CB_EXCEPT_CTL
        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?

HTH,

Heath

[chrisrlong]

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.

- Chris

[alison]

Thanks all for your responses,

I will look into another way of performing this task.

Regards as Always

Allie