related ranking problem....

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am making a report built on querie that shows company_name, sales, assets, assets order. The report must shows all this fields based on sales sorted descending, the problem is: sometimes when there are 2 companies has same assets figure, assets order calculated and appear same figure, for example

Company name Sales Assets Assets orde
Comp 1 5000 10000
Comp 2 4500 12000
Comp 3 4000 9000
Comp 4 3500 12000

This is the report, comp2 & comp4 has same assets but comp2 sales is higher than comp4 sales and the assets order same for both
I need the report to show that comp4 assets order as 2 because its sales is less than comp
How?
The assets order SQL as follows
assetsord: (select count(*) from companyinfo where companyinfo.assets > comp1.assets)+
 
ana said:
I am making a report built on querie that shows company_name, sales, assets, assets order. The report must shows all this fields based on sales sorted descending, the problem is: sometimes when there are 2 companies has same assets figure, assets order calculated and appear same figure, for example:

Company name Sales Assets Assets order
Comp 1 5000 10000 3
Comp 2 4500 12000 1
Comp 3 4000 9000 4
Comp 4 3500 12000 1

This is the report, comp2 & comp4 has same assets but comp2 sales is higher than comp4 sales and the assets order same for both.
I need the report to show that comp4 assets order as 2 because its sales is less than comp2
How?
The assets order SQL as follows:
assetsord: (select count(*) from companyinfo where companyinfo.assets > comp1.assets)+1
Click to expand...


I think the ranking subquery should be more like:

(select count(*) from companyinfo where companyinfo.assets >
comp1.assets OR (companyinfo.assets = comp1.assets AND
companyinfo.Sales > comp1.Sales) + 1
 
Back
Top