• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 526
  • Last Modified:

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...
0
WaterAid
Asked:
WaterAid
  • 3
  • 2
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
WaterAidAuthor Commented:
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?

0
 
jvejskrabCommented:

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
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
jvejskrabCommented:

maybe with more columns in the table, it would be more effective to you query like this

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

so you can group only by columns which are desired to compute the rank, not by all columns
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sure, there is RANK() for sql 2005+, but also non-RANK() versions that will work in sql 2000
0
 
WaterAidAuthor Commented:
Hi...  Thanks for your feedback jvejskrab - your solution was very helpful!  

Here's the full syntax I used in the end should anyone else be searching for a similar answer:


DROP TABLE #Temp1
DROP TABLE #Temp2

SELECT IDENTITY (INT,1,1) AS SeqNo, CUSTOMERID, PURCHASEID INTO #TEMP1
FROM dbo.Purchases
ORDER BY   CUSTOMERID, PURCHASEID ASC

SELECT a.CUSTOMERID, a.PURCHASEID,
a.seqno - b.MinSeqNo + 1
AS [RANK]INTO #Temp2
FROM #Temp1 a INNER JOIN
(SELECT [CUSTOMERID], MIN (SeqNo) AS MinSeqNo FROM #Temp1
GROUP BY [CUSTOMERID]) b
ON a.CUSTOMERID = b.CUSTOMERID AND a.SeqNo <= b.MinSeqNo +100
ORDER BY a.CUSTOMERID, a.Seqno  

UPDATE dbo.Purchases SET G_PurchaseRank = (SELECT (RANK) FROM #Temp2 WHERE Purchases.PURCHASEID = #Temp2.PURCHASEID)
0
 
WaterAidAuthor Commented:
Own solution selected as it provides complete syntax for update etc...
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now