Urgent Help needed in Rank function!!!!

Database
Enthusiast

Urgent Help needed in Rank function!!!!

Hi,

My select would look something like this
Select Name,Age, State, Amount from A

I am able to list the top 10 people in terms of amount using the Rank/Qualify function in the following way.

Select Name, Age, State, Rank(Amount) as RanAmount
From A
Qualify RanAmount <=10

But i need to list the top 10 people in each state instead of top ten people at the highest level.

Kindly let me know if there are options to reset the ranking as the state value changes.

Regards,
Annal Tamizhnambi
2 REPLIES
Enthusiast

Re: Urgent Help needed in Rank function!!!!

Was able to do it in the following way
Rank () Over ( Partition by Column name order by column name)

Issue solved!!!
Enthusiast

Re: Urgent Help needed in Rank function!!!!

some thoughts ...

if you were hunting for the money bags ;-) ... you would have to use

order by Amount desc

And, if 209 was the highest amt, and you had values

209,208,207,.....,201, 200, 200

(Total 11 recs with a dup for 200)

you would get 11 people actually .... (lot of money ? :-) )