CODEWITHSUNDEEP
c#sqlvisual-studio-2010
Bader
Bader

Reputation: 207

Last record of orders for specific customer - SQL

i am trying to show the last order for the a specific customer on a grid view , what i did is showing all orders for the customer but i need the last order

here is my SQL code

     SELECT orders.order_id, orders.order_date, 
    orders.payment_type, orders.cardnumber, packages.Package_name,
 orders.package_id, packages.package_price 
    FROM orders INNER JOIN packages ON orders.package_id = packages.Package_ID 
    WHERE (orders.username = @username )

@username get its value from a cookie , now how can i choose the last order only for a cookie value " Tony " for example ?

Upvotes: 1

Views: 231

Answers (2)

Mitch Wheat
Mitch Wheat

Reputation: 300489

SELECT top 1 
   orders.order_id, 
   orders.order_date,  
   orders.payment_type, 
   orders.cardnumber, 
   packages.Package_name, 
   orders.package_id, 
   packages.package_price  
FROM orders 
INNER JOIN packages ON orders.package_id = packages.Package_ID  
WHERE (orders.username = @username ) 
ORDER BY orders.order_date DESC

In fact assuming orders.order_id is an Identity column:

SELECT top 1 
   orders.order_id, 
   orders.order_date,  
   orders.payment_type, 
   orders.cardnumber, 
   packages.Package_name, 
   orders.package_id, 
   packages.package_price  
FROM orders 
INNER JOIN packages ON orders.package_id = packages.Package_ID  
WHERE (orders.username = @username ) 
ORDER BY orders.order_id DESC

Upvotes: 1

Schultz9999
Schultz9999

Reputation: 8916

To generalize (and fix a little bit) Mitch's answer, you need to use SELECT clause embellished with TOP(@N) and ORDER BY ... DESC. Note that I use TOP(@N), not TOP N, which means you can pass it as an argument to the stored procedure and return, say, not 1 but N last orders:

CREATE STORED PROCEDURE ...
    @N int
...
SELECT TOP(@N) ...
ORDER BY ... DESC

Upvotes: 3

Related Questions