How to Rank two columns in a query

[doyle60]

I wish to create a ranking column on a field in a query basing the
ranking on two other fields. For example, if I have this:

GroupID CatNumber GrpNumber
1455 5.1 1.0
4433 5.1 2.0
8222 5.1 4.2
5446 8.0 2.0
7119 9.3 4.0
2141 9.3 12.0

I would like to rank the results by CatNumber than by GrpNumber,
getting this:

GroupID CatNumber GrpNumber Rank
1455 5.1 1.0 1
4433 5.1 2.0 2
8222 5.1 4.2 3
5446 8.0 2.0 4
7119 9.3 4.0 5
2141 9.3 12.0 6

GroupID is unique.

Searching this group and googling, I cannot find anything exactly like
this. There is a chance that users will misnumber things and create a
tie (I haven't made that impossible for several reasons). In such a
case, I prefer the ranking to simply continue in numeric sequence
without skipping a number or repeating a number, but I will take what I
can get. Thanks in advance,

Matt

 

[John Nurick]

Hi Matt,

Here's an example that works in the Northwind sample database and does
what I think you're asking. It produces a numbered list of Orders by
OrderDate, and within each value of OrderDate by ShippedDate. I think
you'd substitute CatNumber for OrderDate and GrpNumber for ShippedDate.

SELECT
(SELECT COUNT(B.OrderDate)
FROM Orders AS B
WHERE B.OrderDate < A.OrderDate)
+ (SELECT COUNT(C.OrderDate)
FROM Orders AS C
WHERE C.OrderDate = A.OrderDate AND C.ShippedDate < A.ShippedDate)
+ 1 AS SEQ,
A.OrderID, A.OrderDate, A.ShippedDate, A.Freight
FROM Orders AS A
ORDER BY A.OrderDate, A.ShippedDate
;

 

[doyle60]

Thanks John. I finally got to this and was able to make it work. This
query actually wrote page numbers for a report that is really 9 reports
combined.

(For anyone paying attention, I now have that crazy photo catelog done,
designing each page according to the number of photographs. A lot of
work for making a neat look but well worth it. My salesreps will have
no idea how hard it was and will probably just be annoyed that it does
not print out in the proper page order. Oh, well.)

Thanks to all,

Matt