Hi Everyone,

I have typically not bothered with this particular branch of SQL
programming but would like to ask for some guidance on solving a
problem. I can certainly mimic a solution I find on the web, but I
would like to understand the mechanics of the solution so that I can
properly adapt it and have so far not found a suitable explanation.

The scenario is simply this: I have a table with four columns (for
example). I need to generate a ranking report for the data in these
columns, and restart the ranking based on the value of one of the
columns. For example:

Category | SubCategory | Product | SalesValue
MotorBikes | Off-Road | KLR-250 | 100000
MotorBikes | Off-Road | KLR-500 | 27000
MotorBikes | Cruiser | TR-1300 | 190000
QuadBikes | GrassHopper | 750cc | 15000
QuadBikes | GrassHopper | 500cc | 122000
QuadBikes | DuneKiller | 5.7L | 275000
MotorBikes | CafeRacer | Desmo | 325000

The desired output would be to have the data ranked by sales value for
each product per category, per subcategory. So the output would look
like this:

Category | Subcategory | Product | Rank |
Sales Value
MotorBikes | CafeRacer | Desmo | 1
122000
QuadBikes | GrassHopper | 750cc | 2
This is a fairly typical use scenario I guess, but so far I have
reliaed on other systems to perform the ranking functions (such as BI
systems, PIVOT type products etc...) and not ever implemented this
myself. I want to change that oversight on my part and fill the
knowledge gap.

Can you help me?

Cheers

The Frog

The Frog wrote:
Unfortunately your sample data does not include any ties within the
subcategories, so you did not show us how you intended ties to be handled.
There are two general ways to approach this problem:
1. VBA code that loops through a recordset, keeping track of category and
subcategory and incrementing a rank variable for the items therein
(resetting the variable when a new category and subcategory is encountered),
inserting the data with the ranking value into a new table on which the
report is based.
With this technique, you can handle ties pretty much any way you want to.

2. Using a correlated subquery to count the number of records where
salesvalue is less than or equal to the salesvalue in the current record

select category,subcategory,product
(select count(*) from tablename as q
where q.category=o.category and q.subcategory=o.subcategory
and q.salesvalue<=o.salesvalue) as rank
,salesvalue
from tablename as o
order by category,subcategory, salesvalue desc

With this technique, ties would be assigned the same rank.

Bob,

Thanks for the walkthrough. I appreciate it. Having a tied set of
values doesnt matter for the ranking as if I understand the SQl
correctly then it should give them all the same rank, and then the
next 'lowest' score would be a jump down in rank by the normal
increment of one for each of the 'regular' results, and also one for
each of the tied results. This might mean that I have three results at
rank 7 and then the next value for rank for the next item that is not
a tie would be rank 10.

Does that stand to reason? Did I understand it correctly?

Do you find that the correlated subquery is slow to run in your
experience?

Cheers

The Frog

ps: Yours is the first explanation has actually made sense to me.
Thankyou for sharing.

The Frog wrote:

issue. It certainly could be an issue given that the subquery has to run for
every row in the data set. You might find that the VBA looping solution is
the way to go in such a situation.

If porting this to SQL Server is an option, SQL 2005 introduced a new
ROW_NUMBER() function that includes an OVER clause that allows ranking by
"partition". It would be used like this:

SELECT category
, subcategory
, product
, rankno = row_number() OVER (PARTITION BY category,
subcategory ORDER BY salesvalue DESC)
, salesvalue
FROM dbo.ProductSales AS ps
ORDER BY category
, subcategory
,salesvalue desc

I've seen reports of excellent performance against very large data sets.
With this function ties are broken, either by the "order by" in the OVER
clause or by some internal ordering of the rows if the order by is not
sufficient to clarify the final order, so that each row in each partition
result is given a unique rank. The purpose of the row_number function is to
provide a row identifier, not a rank, so this solution might not meet your
needs.
Good. You're welcome.

SQL Server has native Rank functions.. Move to Access Data Projects,
kid
Jet doesn't support Rank functionality (without writing a bunch of
ugly SQL), mainly because it hasn't gotten any new features in the
past 15 years.





On May 2, 1:14*am, The Frog <mr.frog.to.... (AT) googlemail (DOT) com> wrote:

As I said I typically use other tools for the Rank function. Oracle
has a similar capability, SQL Server, and many other tools used in BI
too. This is not actually for an access project but a simple extension
for an Excel macro that is better done in temporary DB than in Excel
itself. A bit of DAO and you avoid hideous amounts of VBA looping
which is extremely slow, as well as having to use Excels row / column
data handling which is also extremely slow. This process just isnt
worth dumping into a full DB solution as it is ad-hoc used, small
scale, and non critical, and department limited. For comparison the
Rank function in both SQL Server and Oracle operates about 1000x
faster than the SQL done in Jet.

Cheers

The Frog

Access Data Projects provide EASIER development kid

you can't sit there and say 'its not worth it'

ADP / SQL makes your life EASIER








On May 4, 12:23*am, The Frog <mr.frog.to.... (AT) googlemail (DOT) com> wrote:

If you have SQL Server and a Web Server to work with. If you dont, you
cant. ADP is also completely useless inside of a macro in Excel - they
have nothing to do with each other; and therefore cannot solve this
problem.

FWIW if I were to create a web page based solution I would either
create a Java based solution and deploy it via Tomcat, or a simpler
PHP / MySQL approach. ADP is kiddie stuff to me and reeks of
amateurism. If you want to do web development properly then learn to
code properly in Web standards - of which ADP is not a part.

The Frog

what the fuck are you talking about RETARD?

he's asking how to do RANK using Access, and the CORRECT ANSWER IS TO
USE ACCESS DATA PROJECTS WITH SQL SERVER 2005 OR NEWER


On May 20, 2:02*am, The Frog <mr.frog.to.... (AT) googlemail (DOT) com> wrote:

You show again the sheer stupidity that your do every time you open
the garbage pit you call a mouth. Keep your hand off your doodle
dickhead. Since you are too dumb to see that I started this thread and
you dont know the actual extend of the scenario to be solved then
anything you say is complete rubbish. You havent been given enough
information to know otherwise. Web server and SQL server would be one
possible solution to what I am trying to achieve, but since neither
exist in my office they are of no use to me. ADP is also of no use to
me. DAP is of no use to me. But then as usual you open your mouth
before knowing what you are talking about. Hand off the doodle and
head out of your arse dickhead.

The Frog