Maximum ROWNUM Query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Maximum ROWNUM Query

Hybrid View

  1. #1
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    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

  2. #2
    Join Date
    Jan 2001
    Posts
    3,131

    Lightbulb

    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]

  3. #3
    Join Date
    Jan 2001
    Posts
    3,131

    Smile

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

  4. #4
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    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

  5. #5
    Join Date
    Apr 2001
    Posts
    118
    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

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  7. #7
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    Thanks all for your responses,

    I will look into another way of performing this task.

    Regards as Always

    Allie

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width