At first glance… All you need is a GROUP BY clause with the MAX aggregate function: It’s never that simple, is it? I just noticed you need the content column as well. This is a very common question in SQL: find the whole data for the row with some max value in a column per some group identifier. I heard that … Read more
I think this is what you’re looking for. NEW_BAL is the sum of QTYs subtracted from the balance: If you want to update the item balance with the new balance, use the following: This assumes you posted the subtraction backward; it subtracts the quantities in the order from the balance, which makes the most sense … Read more
Yes, this is a common aggregation problem. Before SQL3 (1999), the selected fields must appear in the GROUP BY clause[*]. To workaround this issue, you must calculate the aggregate in a sub-query and then join it with itself to get the additional columns you’d need to show: But you may also use window functions, which looks simpler: The … Read more
019 UPDATE: In the 10 years since I wrote this answer, more solutions have been uncovered that may yield better results. Also, SQL Server releases since then (especially SQL 2012) have introduced new T-SQL features that can be used to calculate medians. SQL Server releases have also improved its query optimizer which may affect perf of … Read more
You can use GROUP BY SalesOrderID. The difference is, with GROUP BY you can only have the aggregated values for the columns that are not included in GROUP BY. In contrast, using windowed aggregate functions instead of GROUP BY, you can retrieve both aggregated and non-aggregated values. That is, although you are not doing that in your example … Read more
2019 UPDATE: In the 10 years since I wrote this answer, more solutions have been uncovered that may yield better results. Also, SQL Server releases since then (especially SQL 2012) have introduced new T-SQL features that can be used to calculate medians. SQL Server releases have also improved its query optimizer which may affect perf of … Read more
They’re used in different places. group by modifies the entire query, like: But partition by just works on a window function, like row_number: A group by normally reduces the number of rows returned by rolling them up and calculating averages or sums for each row. partition by does not affect the number of rows returned, … Read more