I assume you start with the second field and want to get the first and third
one, right?
I did tested the following (air code). May miss parenthesis or something
like that.
To get the third column, you can start with a driver table, a table, say
Iotas, with one field, iota, with values from 1 to, say, 99.
===============================
SELECT x.sku, Iotas.iota
FROM (SELECT sku, COUNT(*) As hm
FROM yourInitialTable
GROUP BY sku) AS x
INNER JOIN iotas
ON x.hm >= iotas.iota
===============================
should supply the second and third columns. It is a basic non-equi join. The
idea is to 'compress' the original data, to have no dup any more, here,
virtual table x has only one record per sku, then, to re-hydrate the thing
through the driver table supplying the 'counter'.
To get the first column, start with a query giving a list of distinct sku:
=====================
SELECT DISTINCT sku FROM yourInitialTable
=====================
Saved it under the name qud.
Then, to get the first column (together with the third one), try something
like:
========================
SELECT (SELECT COUNT(*)
FROM qud
WHERE sku <= x.sku) AS counter,
x.sku,
Iotas.iota AS record_counter
FROM (SELECT sku, COUNT(*) As hm
FROM yourInitialTable
GROUP BY sku) AS x
INNER JOIN iotas
ON x.hm >= iotas.iota
=============================
Note that it is a case where the sub-query cannot be immediately promoted
into a join.
Alternative.
It may be faster to do it with a join, but as pointed out, we need an extra
query before being able to do it.
The extra query:
-----------------------
SELECT a.sku, COUNT(*) AS rank
FROM qud AS a INNER JOIN qud AS b
ON a.sku >= b.sku
GROUP BY a.sku
-------------------------
saved as qrank and the top most query would be:
===============================
SELECT qrank.rank, x.sku, Iotas.iota
FROM
((SELECT sku, COUNT(*) As hm
FROM yourInitialTable
GROUP BY sku) AS x
INNER JOIN iotas ON x.hm>= iotas.iota)
INNER JOIN qrank ON x.sku=qrank.sku
===============================
Hoping it may help,
Vanderghast, Access MVP
