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!
This Post Has One Comment
Nice one Rob 🙂 Good solution…
The thing that motivated me to post that was how, when presented with the problem I jumped down the codegen path because there was a lot of code gen in my life at the time – hope I didn’t give the impression I thought it was the most technically beautiful solution 🙂
Like i said, I was weak – trying to kick the habit 😉
Best regards,
James