Rank problem

[natncm70]

Hello,
I have some problem in this following query. In this query, I am taking top 5% of customers and i need to rank it accordingly(like 1,2,3,4 ....). But, I am not getting proper order in this query. Please help me .

select
t3.branch
,t1.acct_nbr
,rank(t1.acct_nbr) as rank_acct_nbr
from
(
SELECT
A.account_nbr
, A.bal_amt
FROM
(
SELECT a1.account_nbr, quantile(100,a1.bal_amt) as percentile
FROM table a1
GROUP BY a1.account_nbr
QUALIFY percentile between 0 and 4
)

) t1 (acct_nbr,balance_amt)
inner join
(
select
a.account_nbr
, a.domicile_channel_id
, a.account_type_code
, b.product_type_code
from
table1 a
inner join table2 b
on a.product_id = b.product_id
) t3(acct_nbr,branch,acct_type_code,prodt_type_code)
on t1.acct_nbr = t3.acct_nbr

 

[BillDHS]

I don't have my reference material handy, but are you missing a GROUP BY if you want the top 5 by branch?

What do you mean by "not getting proper order"?

 

[rrrkrishnan]

I think the rank function should be applied to the bal_amt and not acct_nbr.

Krishnan

 

[natncm70]

Hello,

Eventhough, I have tried with bal_amt in rank function. It is started from total number of records(50,000) then it decreasing one by one(50,000,49,999,49998....).what should i use for that.

Regards

 

[rrrkrishnan]

Try

select
t3.branch
,t1.acct_nbr
,rank(bal_amt) as rank_bal_amt
......
.....
order by rank_bal_amt

 

[rrrkrishnan]

If possible, please post the first five records of your result set, for us to better understand your problem.

Krishnan

 

[natncm70]

Hello Krishnan,

This is the Recordset like this.

Branch Bal_amt rank
1 50,000 1 ( I need rank like this, But in the query is not coming like "1&quot
1 49,000 2
2 47,000 3
3 34,000 4
3 34,000 4
4 32,000 5
5 30,000 6
5 24,000 7
6 20,000 8
like this I need the rank, Branch and Bal_amt is working fine , only thing I need the rank for appropriate bal_amt. Please help me for this.

Regards

 

[rrrkrishnan]

Assuming that there are only six rows in the table,
select
t3.branch
,t1.acct_nbr
,rank(bal_amt) as rank_bal_amt
......
.....

should result in
1 50,000 1
1 49,000 2
2 47,000 3
3 34,000 4
3 34,000 4
4 32,000 5


select
t3.branch
,t1.acct_nbr
,rank(bal_amt asc) as rank_bal_amt
......
.....

should result in
4 32,000 1
3 34,000 2
3 34,000 2
2 47,000 3
1 49,000 4
1 50,000 5


To avoid duplicate acct_nbr in the result set use group by branch,acct_nbr and apply rank function to sum(bal_amt).

If his doesn't work for you, please mail the sample of your result set and not your expected result.

Thanks
Krishnan