CODEWITHSUNDEEP
sql-server-2008sql-order-byranking
sql_dev1802
sql_dev1802

Reputation: 35

How to get last but one record for all customers - SQL Server 2008

I need to get all the prior order history (last but one) of customers from the order table. The issue is there is no customerId or a unique value for the customers. Therefore the only way we can get the information is through customer phone number, customer address, customer name together. I wrote the below query to pull out data for a single order number but I need to know how I should use this query to pull out 10,000 customer records.Please help!

SELECT TOP 1 
    O1.*
FROM 
    Orders AS O1
INNER JOIN 
    Orders AS O2 ON  O1.CustAddress = O2.CustAddress
                 AND O1.CustName = O2.CustName
                 AND O1.CustPhone = O2.CustPhone
                 AND O1.OrderDate < O2.OrderDate
WHERE 
    O1.OrderId = '12345'
ORDER BY 
    O1.OrderDate DESC

Upvotes: 0

Views: 650

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460288

You can use ROW_NUMBER and a CTE:

WITH CTE AS
(
     SELECT O1.*, RN = ROW_NUMBER ()  OVER (
                           PARTITION BY CustAddress, CustName, CustPhone 
                           ORDER BY OrderDate DESC )
     FROM Orders AS O1
     WHERE O1.OrderId = '12345'
)
SELECT * FROM CTE WHERE RN = 1

This returns one row per CustAddress, CustName, CustPhone-group, the latest.

Upvotes: 2

Related Questions