Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle SQL query - Grouping function to take 1st value for non-grouped cols
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
![]() |
![]() |