Re: SQL Query Question

Here is a solution:

For simplicity, first

    create view V as
    Select PersonId, sum(WeeksSalary) TotalSalary     from your_table
    group by personID

Now, to get the ranking, use the following:

    select count(*) Rank, a.PersonID, a.TotalSalary     from V a, V b
    where a.TotalSalary <= b.TotalSalary     group by a.PersonID, a.TotalSalary
    order by Rank

Regards
Suresh

bwally_at_ipass.net wrote:

> 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
Received on Thu Apr 16 1998 - 21:49:52 CDT