CODEWITHSUNDEEP
sqlsql-server-2005sql-server-2008
opticon
opticon

Reputation: 3614

Query lines based on version number

I've got a query that returns the following results:

6464EFD1-413B-4AE2-811D-700A035BF3DB    111102ABF00002D scott       butty       2
DC37D185-0A47-4E19-8FEC-A86428987418    111102ABF00005D NotActive   Attwell     1
0F293E29-5E5B-4C0D-BC25-D05CDB5D6709    111102ABF00002D scott       butty       1
GUID                                    Worksheet       Name              Version

The last column is a version number. I only want the most recent version to be returned - in this case, the bottom row would be omitted, and only the top two would be pulled. Any help would be greatly appreciated!

Upvotes: 0

Views: 62

Answers (2)

user596075
user596075

Reputation: 

select *
from
(
    select *,
        row_number() over(partition by worksheet order by version desc) as rownum
    from yourTable
) a
where rownum = 1

Upvotes: 1

bv8z
bv8z

Reputation: 975

SELECT *
FROM MyTable A
WHERE A.Version = (SELECT MAX(Version) AS Version FROM MyTable B WHERE B.Worksheet = A.Worksheet)

Upvotes: 1

Related Questions