Convert dense_rank and row_number sql server functions to access

[Hatic]

Hi,
How can I convert these two rows to access sql?

dense_rank() over(partition by field1 order by field2) as name1,

row_number() over(partition by fld1, (dense_rank() over(partition by fldnm1
order by fldnm2)) order by fld2) as name2

thanks in advance

 

[vanderghast]

A dense_rank() is logically the same as ranking without duplicated values
and then, join the initial data with the previous result you got.

The word partition, in this case, is the syntax used by MS SQL Server to
signify that the rank has to be performed 'by group' (as example, in each
country, rank the cities by their population, to the largest city, in each
country, will get a rank of 1.


So, assume q1 supply the values without dup:

SELECT f1, f2
FROM somewhere
GROUP BY f1, f2


as example, then, you can rank group by f1 with your favorite method, I
prefer using joins:

SELECT a.f1, a.f2, COUNT(*) AS dense_rank
FROM q1 AS a INNER JOIN q1 AS b
ON a.f1=b.f1
AND a.f2 >= b.f2
GROUP BY a.f1, a.f2

(note that here, rank = 1 to the lowest value, not the highest. To reverse
the ordering, change >= to <= in the ON clause)

saved as qrank, then


SELECT *, qrank.dense_rank
FROM somewhere INNER JOIN qrank
ON somewhere.f1=qrank.f1
AND somewhere.f2=qrank.f2


return your initial data WITH the dense_rank value, by group. You can add an
ORDER BY clause, for visual inspection about the validity of the result:

ORDER BY somewhere.f1, somewhere.f2


Vanderghast, Access MVP

 

[vanderghast]

Forgot the row_number(): As well as for row_number() goes, it is logically
the same as ranking the primary key (and if you use 'partition', that
ranking as to be computed "by group').


Vanderghast, Access MVP

 

[Hatic]

thanks a lot
I tried your code it is working for
dense_rank() over(partition by field1) as name1
but I couldn't add order by. when I add it just changes the apperance nor
the rankings
dense_rank() over(partition by field1 order by field2) as name1
this page includes a data table which describes their difrences
http://thehobt.blogspot.com/2009/02/rownumber-rank-and-denserank.html

 

[vanderghast]

The ORDER BY is required in MS SQL Server to describe the GROUP, but in the
code I supplied, that functionality is supllied by the ON clause of the join
computing the ranking:

(hoping the newsreader don't break the line... )

MS SQL Server: ... rank() (over partition f1 order
by f2 )
| |
|
JET, inner join: ... COUNT(*) ... ON a.f1=b.1 AND a.f2 >= b.f2
GROUP BY a.f1


If you prefer, the 'partitions', for wich the rank start all over, are
define and in the GROUP BY clause, and by a strict equality in the ON
clause, while the ORDER BY is just described in the ON clause.

Maybe a more complex example may help:

( over partition f1 order by f2, f3 )


then the ON clause become complex too:

... ON a.f1=b.f1 AND (a.f2>b.f2 OR ( a.f2=b.f2 AND a.f3>= b.f3))
GROUP BY a.f1

since the ON clause, by syntax, describe what the strict order is, rather
than just refering to it, as concept, as an ORDER BY clause does: to obtain
the ordering, consider the field f2 and, if there is equality, consider then
the field f3:


(a.f2>b.f2 OR ( a.f2=b.f2 AND a.f3>= b.f3))
consider f2 but if equal consider f3



In other words, there is no **explicit** ORDER BY in the JET equivalence,
since it is **implicit** in the ON clause.


Hoping it makes sense.
Vanderghast, Access MVP