Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2005
    Posts
    1

    Unanswered: get maximum query

    Hello, i have a table which primary keys is formed by an id and a date. It contains other fields.

    Given a selection of several ids, i need to get the line where the date is the oldest for each id

    I'm trying desperatly to get something but nothing works

    Many Thanks

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    Depends on what how the id and date is separated, but you would want to substr/instr it to separate the id and date. You could then find the id, and max(date).

    So given something like this:
    Code:
    FIELD: ID_SUBSTR
    1|17-MAY-2005
    1|18-MAY-2005
    1|19-MAY-2005
    2|17-APR-2004
    2|17-APR-2005
    2|18-APR-2005
    You would want a query that looked a little something like this:
    Code:
    Select substr(id_substr,1,1) your_id, 
             max(to_date(substr(id_substr, instr(id_substr, '|')+1))) oldest_date
    from your_table
    group by substr(id_substr,1,1)
    This is off the top of my head, and havent tested but I think I closed all the parentheses. So basically you want to group by the ID, and find the max date on the other split. Make sure you include the to_date part so you check on the actual date because you are storing this pk in varchar2 field.

    Hope that gives you an idea!
    Last edited by ss659; 01-24-05 at 15:46.
    Oracle OCPI (Certified Practicing Idiot)

Posting Permissions

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