| Ranking report [message #82670] |
Sat, 21 June 2003 04:52  |
ash
Messages: 43
Registered: February 2001
|
Member |
|
|
Hi,
I got this report which I have to make. I cant understand how do I rank different products based on different criteria.
Eg.
Based on sales
Prod# Description Rank Sales Stock
1 ABC 1 1000 1000
2 XYZ 2 999 1001
3 PQR 3 87 10000
Based on stock
Prod# Description Rank Sales Stock
1 PQR 1 87 10000
2 XYZ 2 999 1001
3 ABC 3 1000 1000
Please help
Ash
|
|
|
|
| Re: Ranking report [message #82682 is a reply to message #82670] |
Sun, 22 June 2003 23:09  |
Shailender Mehta
Messages: 49
Registered: June 2003
|
Member |
|
|
You will have to write 2 queries :-
Name Null? Type
------------------------------- -------- ----
PRODUCT# NUMBER
PRODUCT_DESC VARCHAR2(100)
SALES NUMBER
STOCK NUMBER
1) Rank on stock
select 'x', product#, product_desc, sales, stock,
rank() over (partition by 'x' order by stock desc) as rk
from temp
/
' PRODUCT# PRODUCT_DESC SALES STOCK RK
- --------- -------------------- --------- --------- ---------
x 3 PQR 87 10000 1
x 2 XYZ 999 1001 2
x 1 ABC 1000 1000 3
2) Rank by sales
select 'x', product#, product_desc, sales, stock,
rank() over (partition by 'x' order by sales desc) as rk
from temp
/
' PRODUCT# PRODUCT_DESC SALES STOCK RK
- --------- -------------------- --------- --------- ---------
x 1 ABC 1000 1000 1
x 2 XYZ 999 1001 2
x 3 PQR 87 10000 3
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
