CODEWITHSUNDEEP
sqlsql-serversql-server-2005sql-server-2008t-sql
Jared
Jared

Reputation: 2483

T-SQL Conditional IF statement

I have a recordset that looks like this:

DocID (Primary Key) | Version | Name
4 | 1 | FileNameA
8 | 2 | FileNameA
6 | 1 | FileNameB
3 | 1 | FileNameC

What I need to do is only show a unique file name where the version number is greater than 1.

So I need a SQL query that would return a recordset that looks like this:

DocID (Primary Key) | Version | Name
8 | 2 | FileNameA
6 | 1 | FileNameB
3 | 1 | FileNameC

All I am using now is a basic select statement from my table. I am not sure if this is possible. Any help would be greatly appreciated.

Upvotes: 2

Views: 541

Answers (1)

JNK
JNK

Reputation: 65157

Get your desired rows from a subselect and join on that:

SELECT DocID, Version, Name
FROM MyTable t
INNER JOIN (SELECT Name, MAX(Version) as Version
            FROM MyTable
            GROUP BY Name) sub
  ON Sub.name = t.name
  AND sub.version = t.version

Upvotes: 1

Related Questions