chrismic999 (5/27/2014)
This seems to workSELECT accountname,invoice_date, ROW_NUMBER()
OVER(PARTITION BY AccountName Order by INVOICE_DATE) as RankOrder from dbo.sagesales
where AccountName is not null
Insert help from above
But now I want to update a field called "Place" (int) in dbo_sagesales with the result ie the ranked order
thanks
alternatively I would like to extract only those that have a ROW_Number as 1?
I tried where AccountName is not null and Rankorder =1 but it didnt like it in the above query!
You can't reference windowing functions in the where clause. If you only want where the first one then I would use MIN like suggested above. No need to go to all the trouble to number all the rows just to throw most of them away anyway. Look at the code David posted. It is exactly what you need here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/