• chrismic999 (5/27/2014)


    This seems to work

    SELECT 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/