CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Erik Lindbøg-Løfgreen
Erik Lindbøg-Løfgreen

Reputation: 69

SQL Server 2008 newest order from a specific customer

SELECT OrdreID, KundeID, OrdreDato, Navn, Farve
FROM   Ordreliste
WHERE  Newest Order date By ????

I need to get the newest order date by a specific customer, but I am totally blank !

Upvotes: 1

Views: 65

Answers (3)

Charl
Charl

Reputation: 1002

If you want to have a list of the last orders for more than 1 customer (Example: Display the last order dates for everyone from a spesific country), you can use a common table expression with a ranking function:

; WITH CTE_Last_Orders AS ( SELECT ROW_NUMBER() OVER (PARTITION BY KundeID, ORDER BY KundeID DESC) AS LastOrder ,OrdreID, KundeID, OrdreDato, Navn, Farve FROM Ordreliste WHERE KundeID IN () ) SELECT KundeID, OrdreDato FROM CTE_Last_Orders WHERE LastOrder = 1;

Upvotes: 0

user743382
user743382

Reputation:

To suggest another possibility:

SELECT TOP 1 OrdreID, KundeID, OrdreDato, Navn, Farve
FROM   Ordreliste
WHERE  KundeID = (your customer)
ORDER BY OrdreDato DESC

If the customer has two orders on the same date, you will only get one, but it may not be the latest one. If orders are entered sequentially, you can avoid this by instead ordering by OrdreID DESC, which should always be unique.

Upvotes: 3

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

To get the latest date for a specific customer you can use the MAX aggregate function.

SELECT MAX(OrdreDato)
FROM   Ordreliste
WHERE  Customer = 'YourCustomer'

MAX
Returns the maximum value in the expression.

Upvotes: 1

Related Questions