Last Function in Group By Query in Oracle

Farzad Akbarnejad
Farzad Akbarnejad used Ask the Experts™
on
Hello,

I have a table with the following data:


            RWSHID            RISUID       JOBCODE
  ---------------------------------------------------------
      0188280314      0010202751      000000
      0188280314      0010202751      024019
      0188280314      0010202751      024043
      0188280314      0010424521      000000
      0188280314      0010424521      024043
      0188280314      0010424521      027212
      0188280314      0010454707      007005
      0188280314      0010454707      024019
      0188280314      0010454707      024043
      0188280314      0010661047      024043
      0188280314      0010661047      027075
      0188280314      0010667463      024019
      0188280314      0010667463      024032
      0188280314      0010667463      024043
      0188280314      0010684834      000000
      0188280314      0010684834      001094
            ...........           ...............       ...........
            ...........           ...............       ...........
            ...........           ...............       ...........
            ...........           ...............       ...........

I want to distinct (or group by) on RWSHID and RISUID and select last JOBCODE in each row.
Would you please send me the query run in Oracle (PL/SQL)

Thanks
-FA


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009
Commented:
select RWSHID, RISUID, MAX(JOBCODE) FROM MYTAB
  GROUP BY RWSHID, RISUID

Commented:
>I want to distinct (or group by) on RWSHID and RISUID and select last JOBCODE in each row.

But there is only one JOBCODE in each row.
And what do you mean by "last"; there is no date in evidence

Is this what you mean:

Select  RWSHID, RISUID max(JOBCODE) group by  RWSHID,  RISUID              
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
you might want to read this article: http://www.experts-exchange.com/A_3203.html
will also show how to do it (with row_number() function)

this will be very useful if you need other rows also from the same table.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial