0

I just wanted to double check this SQL query that I wrote. I want to return the top five customer first names based on the dollar amount ordered? I'm using a table called "customer" and table called "orders". However, I can't remember if I need to use a "max" somewhere.... Any help is appreciated!

SELECT TOP 5
customer.customerFirstName

FROM customer
LEFT JOIN orders
    ON customer.customerID = orders.customerID

ORDER BY orders.orderCost DESC
Rob B
  • 27
  • 1
  • 2
    Did you run it and see what happens? – Munir Dec 24 '15 at 02:44
  • 1
    Sample data and expected result will help – Pரதீப் Dec 24 '15 at 02:44
  • 1
    Your query looks fine. – sqluser Dec 24 '15 at 02:45
  • Unfortunately, I only have access to sql server on my work computer and I'm currently using my home computer to post this. I just wanted to double check to make sure the logic I'm using makes sense. – Rob B Dec 24 '15 at 02:47
  • If you still wonder why, read about the order of execution: "ORDER BY" executes before "TOP" http://stackoverflow.com/questions/4596467/order-of-execution-of-the-query – Sam Segers Dec 24 '15 at 02:48
  • You may have more than 5 customers with the same value for `orderCost`, being it the maximum in the database. If it happens, do you still must select the top 5? Which 5 customers must be selected in this case... any of them? – Felypp Oliveira Dec 24 '15 at 03:39

2 Answers2

1

You need a group by, I think:

SELECT TOP 5 c.customerFirstName
FROM customer c LEFT JOIN
     orders o
     ON c.customerID = o.customerID
GROUP BY c.customerFirstName
ORDER BY SUM(o.orderCost) DESC;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

i think this should help answer your question, http://www.w3schools.com/sql/sql_join_left.asp

SELECT TOP 5 orders.orderid,

orders.customerid, customers.customername From customers LEFT JOIN orders ON customers.customerid=orders.customerid ORDER by orders.orderid DESC

learningAlways
  • 33
  • 7