Removing all the rows bar one per customer from a table
I stumbled upon James Green's post about T-SQL and couldn't help but post about it. It's not that I want to blog about his blog, but rather I want to jump up and down about the fact that he's missing one of the best things about SQL2005 - the OVER() clause!
James' task is to remove all the rows from a table, except the lowest number order for each customer.
He has written a query to generate code to do this, and then copies the results into another query which he then executes. All well and good as an exercise is code generation, but I just want him to use:
with CTE_OC as
(select *, row_number() over (partition by customer_id order by customer_id, order_id) as rn from OrderCustomer)
delete from CTE_OC where rn > 1
Which does the whole thing in one step. The row_number() bit gives a number to each row, starting again at one for each new customer. So then you just delete all the ones that aren't the first one for each customer. Easy, James!