Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle SQL query - Grouping function to take 1st value for non-grouped cols

Re: Oracle SQL query - Grouping function to take 1st value for non-grouped cols

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 07 Sep 2005 13:24:22 -0700
Message-ID: <1126124612.163411@yasure>


wesley.d.gibbs_at_gmail.com wrote:
> What Oracle sql query would take data in a table
> like follows:
>
> DEPARTMENT JOB Total_Empl Average_Sal
> Accounting AC_ACCOUNT 1 99600
> Accounting AC_MGR 1 144000
> Accounting All Jobs 2 121800
> Administration AD_ASST 1 52800
> Administration All Jobs 1 52800
> Executive AD_PRES 1 288000
> Executive AD_VP 2 204000
> Executive All Jobs 3 232000
> Finance FI_ACCOUNT 5 95040
> Finance FI_MGR 1 144000
> Finance All Jobs 6 103200
>
>
> and produce data like follows:
>
> Accounting AC_ACCOUNT 1 99600
> Administration AD_ASST 1 52800
> Executive AD_PRES 1 288000
> Finance FI_ACCOUNT 5 95040
>
>
> I want something like this:
>
>
> select DEPARTMENT, First(JOB), First(Total_Empl),
> First(Average_Sal)
> from table
> group by DEPARTMENT
>
> I want to group by department, then take the first record for the
> remaining columns.
>
> Any idea? Is their a group function that would do this?

Define first.

Potentially you could add ROWNUM to your query and go for MIN() of the row number but this seems really strange as the result you are trying to achieve is meaningless or misleading.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Wed Sep 07 2005 - 15:24:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US