show the most recent five orders that were purchas

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

show the most recent five orders that were purchas

Author

Topic

sha1023012
Starting Member

4 Posts

Posted - 2011-10-12 : 11:12:56

I am still a noobie when it comes to the SQL server. I know something but other still seem so confusing. I know the more you practice with it the better you get. Well I am working on this part that kinda has my mind confused. It is using northwind database show the most recent five orders that were purchased from a customer who has spent more than $25,000. Ok I know I will use the customer table, and the order table. What is putting my mind crazy is how do you get it where the 5 customers who spent 25,000 up. I am sure I probably know it but my mind is not thinking it does.

The reason why I think this one is hard for me is because I have a order detail which has the OrderID, ProductID, UnitPrice,Quantity, and discounts in it.

then you have Orders which don't have any information with it that deals with the money issue. Then you have the customers which I know I will need to use that to show the 5 customers who spent more than 25,000.

With the Order Detail I don't know how to use it that well because they are split into two names. I am woundering can I put a bracket around it since there is a space between the two words.

I am sure people are going to vote this down or say i seen this on SQL dummies but I am just trying to understand this. I know you can not see my database to see what I am looking at but I will also try in my best to explain why I am so confused. I am just trying to understand this. Thanks to all who looked at this topic.

This is what I have so far I know I am missing to add the 5 in there I am just not understanding where too.

SELECT ContactName FROM Customers
INNER JOIN [Order Details]ON OrderId =
CustomerID
INNER JOIN Orders ON Product.ID = Orders.ID
WHERE UnitPrice >= 25000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 11:54:18

do you mean this? or do you want to consider the whole spend for taking top 5?


SELECT TOP 5  ContactName FROM Customers
INNER JOIN [Order Details]ON OrderId = 
CustomerID
INNER JOIN Orders ON Product.ID = Orders.ID
WHERE UnitPrice >= 25000
ORDER BY CustomerID ASC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sha1023012
Starting Member

4 Posts

Posted - 2011-10-12 : 12:14:40

I need the top 5 orders that were purchased from a customer who has spent more than 25,000.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 12:22:05

[code]SELECT *
FROM
(
SELECT ContactName ,
ROW_NUMBER() OVER (PARTITION BY ContactName ORDER BY OrderPrice DESC) AS Rn,
SUM(OrderPrice) OVER (PARTITION BY ContactName) AS TotalOrderSpend,*
FROM Customers
INNER JOIN [Order Details]ON OrderId =
CustomerID
INNER JOIN Orders ON Product.ID = Orders.ID
)t
WHERE TotalOrderSpend>=25000
AND Rn <=5
ORDER BY CustomerID ASC
[/code]

I've assumed OrderPrice is field containing price for order. make sure you replace it with correct field which represents order price in your query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sha1023012
Starting Member

4 Posts

Posted - 2011-10-12 : 12:36:55

I think i am putting the wrong tables together cause the orderID is comming up with and error Ambiguous column name 'orderID'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-12 : 13:33:45

put an alias for tables and use it in reqd columns also

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources