Only include the latest rows
-
Probably a dumb question, but I can't figure this out. Suppose I have 2 tables: ORDERS(id,cust_id,date,price) CUSTOMERS(cust_id,name) I want to know how much money the customer #3 spent, and how many orders he did, okay: SELECT COUNT(*), SUM(price) FROM orders WHERE cust_id=3 So far so good, now I want to know this, but I want to narrow it down to the latest 50 orders he did (or to the latest x orders if x < 50), how can I do that? SELECT COUNT(*), SUM(price) WHERE cust_id=3 ORDER BY date DESC LIMIT 50 doesn't work, because there's still only 1 row returned. I'm a bit clueless :confused: Thanks for your help!
-
Probably a dumb question, but I can't figure this out. Suppose I have 2 tables: ORDERS(id,cust_id,date,price) CUSTOMERS(cust_id,name) I want to know how much money the customer #3 spent, and how many orders he did, okay: SELECT COUNT(*), SUM(price) FROM orders WHERE cust_id=3 So far so good, now I want to know this, but I want to narrow it down to the latest 50 orders he did (or to the latest x orders if x < 50), how can I do that? SELECT COUNT(*), SUM(price) WHERE cust_id=3 ORDER BY date DESC LIMIT 50 doesn't work, because there's still only 1 row returned. I'm a bit clueless :confused: Thanks for your help!
Limit doesn't work because you are actually groupping everything into a single row. If you want to restrict the data that goes into the group operation, you have to do it in where or for example use inline views. If you want to use where condition, it could be something like:
SELECT COUNT(*), SUM(price)
FROM Orders o1
WHERE cust_id=3
AND 50 <= (select count(*)
from Orders o2
where o2.cust_id = o1.cust_id
and o2.date < o1.date)The need to optimize rises from a bad design.My articles[^]
-
Limit doesn't work because you are actually groupping everything into a single row. If you want to restrict the data that goes into the group operation, you have to do it in where or for example use inline views. If you want to use where condition, it could be something like:
SELECT COUNT(*), SUM(price)
FROM Orders o1
WHERE cust_id=3
AND 50 <= (select count(*)
from Orders o2
where o2.cust_id = o1.cust_id
and o2.date < o1.date)The need to optimize rises from a bad design.My articles[^]
I see. Is subquerying the only solution? I'm not sure this is going to be run very fast.
-
I see. Is subquerying the only solution? I'm not sure this is going to be run very fast.
Tony_P wrote:
Is subquerying the only solution
No it's not. I think there are several ways. Depending on your indexing an inline view may be faster. Then it would be something like:
SELECT COUNT(*), SUM(price)
FROM (SELECT TOP 50 Price
FROM Orders
WHERE cust_id=3
ORDER BY date DESC) tableAliasCheck the execution plan to see which one is better in your case. Additionally add relevant indexes if you need.
The need to optimize rises from a bad design.My articles[^]
-
Tony_P wrote:
Is subquerying the only solution
No it's not. I think there are several ways. Depending on your indexing an inline view may be faster. Then it would be something like:
SELECT COUNT(*), SUM(price)
FROM (SELECT TOP 50 Price
FROM Orders
WHERE cust_id=3
ORDER BY date DESC) tableAliasCheck the execution plan to see which one is better in your case. Additionally add relevant indexes if you need.
The need to optimize rises from a bad design.My articles[^]
Ha right I like this solution. I'm going to benchmark both and see how it goes. Thank you!
-
Ha right I like this solution. I'm going to benchmark both and see how it goes. Thank you!