cpradio's tidbits of information a one stop spot for my personal references

ROW_NUMBER() and PARTITION BY in SQL Server 2000

Here is the below SQL for SQL Server 2000 to select the latest version of a record grouping by a single column.

SELECT *
  FROM (
    SELECT *, (
      SELECT COUNT(*)
        FROM MyTable AS counter
      WHERE counter.PartitionByColumn = MyTable.PartitionByColumn
        AND  counter.OrderByColumn >= MyTable.OrderByColumn
      ) AS rowNumber
    FROM MyTable
  ) AS r1
  WHERE r1.rowNumber = 1

Same code in SQL Server 2005 would look like this:

SELECT * FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY PartitionByColumn
    ORDER BY OrderByColumn DESC) AS rowNumber FROM MyTable) AS rw1
  WHERE rw1.rowNumber = 1