Rank in query 3

[dkillilea]

PHV's SQL for ranking inside a query is VERY USEFUL. The use of subqueries is perfect for solving this dilema....

Now I want to take it further...I need to rank according to:

1. PRODUCT_NUM (ASC)

Within the Product_Group_Name I need to rank according to:

2. INVOICE_DT (ASC)
3. CUST_NAME (ASC)
4. CUST_SHIP_TO_CITY (ASC)

*** So (1) is my main group. (2) & (3) can both have duplicates. So I included (4) so that I won't have a duplicate ranking number within (1) ***

My problem is: How do I rank multiple items within a group and not get a duplicate ranking value within that group.


Here's what I would like to return from my ranking query:

RNK PRODUCT_NUM INVOICE_DT CUST_NAME CUST_SHIP_TO_CITY
1 2 08/04/2005 TONY'S TULSA
2 2 08/15/2005 ACP HOUSTON
3 2 08/15/2005 WALSH LENEXA
4 2 08/27/2005 CURTIS AKRON
5 2 08/27/2005 CURTIS CANTON
1 5 07/31/2005 SUBURBAN KANSAS CITY
2 5 08/11/2005 SUBURBAN DALLAS

Please Please Please...can someone show me the light! I tried modifying thread701-1036015 to no avail.

Thanks,
Doug

 

[PHV]

Perhaps something like this ?
SELECT Count(*) AS RNK, A.PRODUCT_NUM, A.INVOICE_DT, A.CUST_NAME, A.CUST_SHIP_TO_CITY
FROM yourTable As A, yourTable As B
WHERE Format(A.PRODUCT_NUM,'0000') & Format(A.INVOICE_DT,'yyyymmdd') & A.CUST_NAME & A.CUST_SHIP_TO_CITY >= Format(B.PRODUCT_NUM,'0000') & Format(B.INVOICE_DT,'yyyymmdd') & B.CUST_NAME & B.CUST_SHIP_TO_CITY
GROUP BY A.PRODUCT_NUM, A.INVOICE_DT, A.CUST_NAME, A.CUST_SHIP_TO_CITY

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dkillilea]

PHV, your great. I tried a variation of this, but I concatenated in the prior query and chose not to do any formatting...that's probably why it did not work for me. Then I thought I would need another subquery, and that's why I finally turned to you for a solution.

The solution you entered is very simple and works wonderfully. Thanks a million. You know how to git 'r done.

Thanks,
Doug