Finding the latest revision in SQL 2005 database

• scott.owen

  SSC Enthusiast

  Points: 174

  More actions

  August 13, 2008 at 12:17 pm

  #72713

  I have a database containing a table listing the revision history of drawings. Let's say drawing1 has 5 revisions A,B,C,D,and E. I want my query to return only the latest one, in this case "F". What function do I use to do this.

  Thanks for any help that can be offered.

• SwePeso

  SSC-Dedicated

  Points: 39758

  More actions

  August 13, 2008 at 12:27 pm

  #857638

  This is a SQL Server 2000 forum, but you write you have SQL Server 2005.

  SELECT Col1, Col2, Col3

  FROM (

  SELECT Col1, Col2, Col3, ROW_NUMBER() OVER (PARTITION BY DrawingID ORDER BY Revision DESC) AS RecID

  FROM Table1

  ) AS d

  WHERE RecID = 1

  ---

  N 56°04'39.16"
  E 12°55'05.25"