Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL Query Question
Hi,
maybe this statement form Juri Modic give you some hints:
SELECT a.deptno, a.sal sumsal, ROWNUM ranking FROM
(SELECT deptno, SUM(sal) sal FROM emp GROUP BY deptno) a,
dual
WHERE -1*a.sal = DECODE(dual.dummy(+),'X',0,0)
AND ROWNUM <= 3
/
bwally_at_ipass.net schrieb:
>
> Maybe you know how to do this, I can't remember. I have a table like
> this
>
> Week PersonID WeeksSalary
> 1 5571 1200.00
> 1 6001 1575.00
> 1 733 2400.00
> 2 5571 1350.00
> 2 6001 1000.00
> 2 733 2000.00
> 3 6001 1800.00
> 3 5571 1500.00
> 3 733 1000.00
>
> I want to retrieve the sum(WeeksSalary) grouped by personId and sorted
> such that I get a ranking printed, so:
>
> Ranking PersonId TotalSalary
> 1 733 5400
> 2 6001 4325
> 3 5571 4050
> 4 1111 2010
> etc.
>
> I know how to get personId TotalSalary
>
> Select personId, sum(WeeksSalary) TotalSalary
> >From myTable
> Group By personID
>
> How might I get a numeric ranking in for each record?
>
> Mike Wallach
--
Regards
Matthias Gresz :-) Received on Fri Apr 17 1998 - 01:26:43 CDT