asked on

SQL 2000 Ranking

Hi...

SQL Server 2000 - prior to RANK()!!!!!

I've got a table called Purchases. I'd like to rank each of the purchases, by customer, to end up with something like this: Rank-Example.xlsx

There's a separate Customers table I could reference in a subquery if required (Customers.CustomerID = Purchases.CustomerID).

Thanks in advance for your help...

Guy Hengel [angelIII / a3]

this article should help you:
https://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_1555-Analytical-SQL-Where-do-you-rank.html

WaterAid

ASKER

Hi... Thanks for the pointer - that solution seems to point toward the Rank() function in SQL 2005, which I'm unfortunately not party to!

Any further thoughts?

jvejskrab

one of the possible solution is for example

CREATE TABLE #t (
      CustomerID int,
      PurchaseID int,
      CombinedID varchar(50),
      PurchaseDate date
)

INSERT INTO #t SELECT 1001,      2001,      '1001.2001',      '1.1.2000'
INSERT INTO #t SELECT 1001,      2002,      '1001.2002',      '1.2.2000'
INSERT INTO #t SELECT 1001,      2003,      '1001.2003',      '1.3.2000'
INSERT INTO #t SELECT 1002,      2004,      '1002.2004',      '1.4.2000'
INSERT INTO #t SELECT 1003,      2005,      '1003.2005',      '1.5.2000'
INSERT INTO #t SELECT 1003,      2006,      '1003.2006',      '1.6.2000'
INSERT INTO #t SELECT 1003,      2007,      '1003.2007',      '1.7.2000'
INSERT INTO #t SELECT 1003,      2008,      '1003.2008',      '1.8.2000'
INSERT INTO #t SELECT 1004,      2009,      '1004.2009',      '1.9.2000'
INSERT INTO #t SELECT 1004,      2010,      '1004.2010',      '1.10.2000'

SELECT t1.CustomerID, t1.PurchaseID, t1.CombinedID, t1.PurchaseDate, COUNT(*)
      FROM #t t1
             JOIN #t t2
                  ON t1.CustomerID = t2.CustomerID
                   AND t1.PurchaseID >= t2.PurchaseID
      GROUP BY t1.CustomerID, t1.PurchaseID, t1.CombinedID, t1.PurchaseDate

DROP TABLE #t

ASKER CERTIFIED SOLUTION

jvejskrab

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Guy Hengel [angelIII / a3]

sure, there is RANK() for sql 2005+, but also non-RANK() versions that will work in sql 2000

SOLUTION

WaterAid

membership

This solution is only available to members.