getting latest row
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: getting latest row

  1. #1
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401

    getting latest row

    select * from order_services

    order_ID order_histid parts ordered ordered_date
    ---------- ---------- ------------- ---------
    3899 5 2 12-FEB-08
    3899 3 3 11-FEB-08
    3898 3 5 10-FEB-08
    3898 4 6 12-FEB-08
    4200 6 2 10-FEB-08
    4200 7 0 12-FEB-08
    4201 8 9 14-FEB-08


    select * from ( order_ID, order_histid, parts, ordered ordered_date, rank() over (order by parts DESC) as hgh from order_services ) where hgh=1;

    order_ID order_histid parts ordered ordered_date RNK
    ---------- --------- ---------- ---------- ----------
    4201 8 9 14-FEB-08


    but i am looking for highest parts for each order_id

    order_ID order_histid parts ordered ordered_date
    -------- ---------- ------------- ---------
    3899 3 3 11-FEB-08
    3898 4 6 12-FEB-08
    4200 6 2 10-FEB-08
    4201 8 9 14-FEB-08

    i tried with the second query with rank but i only i get highest Part not for each orderid..can any one help

    Thanks

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    here is a hint... max()
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401
    you mean this ..select order_ID, order_histid, parts ordered ,max(ordered_date)
    from order_services
    group by order_ID, order_histid, parts ordered

    didn't worked .. i tried with order by des and rownum .. didn't worked

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    I didn't mean that, that's horrible.

    Here is a second hint, max() is supposed to be used in an inline view -subquery- to help you select the right row from each group.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401
    hello
    Thanks for your reply but i am not sure what you are saying ..
    Do you mind giving me a sample query Please

    Thanks

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Code:
    select  *
    from    order_services a
    where   a.order_id || a.parts_ordered = (select b.order_id || max(b.parts_ordered)
                                             from   order_services b
                                             where  b.order_id = a.order_id
                                             group by b.order_id)
    ;
    Query returns one row per order_id, the row with the highest number of parts_ordered.
    Last edited by PAVB; 02-26-2008 at 09:43 AM.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401
    is there a significance of this.i believe this made difference

    a.order_id || a.parts_ordered

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Oh yes... the whole query made the difference, now it works ;-)
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  9. #9
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401
    "Oh yes... the whole query made the difference, now it works ;-)"

    hey
    i am not taking away your help and big THANK YOU.All i was asking what is the significance of this ?

    a.order_id || a.parts_ordered

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    concatenation.
    Look at the query, concatenation allows you to compare the two versions of the concatenated columns in a single try.

    Test it...
    select 'Hello ' from dual;
    select 'World' from dual;
    select 'Hello ' || 'World' from dual;
    Last edited by PAVB; 02-26-2008 at 10:50 AM.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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