Multiple Rankings in 1 Query

[dmarek]

So I have a database with several tables containing metrics. I group these metrics up into 1 query through a few steps (which is fine).

What I want to do next is create a point system using each Stores rank for each metric from best to worst.

Example:

Store Sales $ Target $ Variance to Target $
1234 $1000 $1500 ($500)

similar setup for 4 other metrics, actual->target->variance.

I want to rank each of the 5 metrics to get a score (which I can do individually in separate queries) then add them together and order by worst to best.

Ideas?


PS My method for doing them individually is something like this:

SELECT table.store,
table.variance,
(SELECT count(*) FROM table AS t1 WHERE table.variance > t1.variance) as RANK_SALES_VAR
FROM table
GROUP BY table.store, table.variance
ORDER BY table.variance asc;

I am trying to take that concept (which works) but instead of having 5 side queries to build 1 master RANK query do them in 1 swoop.

Reason for wanting to do it in as few steps as possible is because i have several rankings to do hierarchical. Meaning Store->District->Region and for different businesses of the company.

Thanks

 

[boblarson]

I do not believe you can do it all in one query. To do it in one query you may need to create a function to do it instead.