CODEWITHSUNDEEP
sqldatabaset-sqlsql-server-2012
ChrisO
ChrisO

Reputation: 5465

TSQL retrieve maximum row based on a combination of the largest of other columns

I have a table in SQL Server 2012 that contains patch notes for different versions of my software. The table looks like this:

VersionID int PrimaryKey Identity
Major tinyint
Minor tinyint
Revision tinyint
Patchnotes nvarchar(MAX)

I need to retrieve the row with the most recent version based on Major, Minor and Revision. For example, version 1.1.4 comes after version 1.1.3 but before version 1.2.1.

Would anyone have any ideas on how to write a query in TSQL to do this? If you could maybe push me in the right direction without completing the entire query, I'd be appreciative. Trying to learn and all that!

Upvotes: 3

Views: 163

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

The 2012 version has implemented the standard OFFSET / FETCH syntax (in previous versions you'll have to use the proprietary TOP):

SELECT *
FROM Version
ORDER BY Major DESC, Minor  DESC, Revision  DESC 
  OFFSET 0 ROWS
  FETCH FIRST 1 ROW ONLY ;

Upvotes: 4

Holger Brandt
Holger Brandt

Reputation: 4354

SELECT TOP 1 * FROM MyTable
WHERE Major<=@MAJOR AND Minor<=@MINOR AND Revision<@REVISION
ORDER BY Major DESC, Minor DESC, Revision DESC

Upvotes: -1

Tim Schmelter
Tim Schmelter

Reputation: 460098

I would order by your three fields descending and then take one record:

SELECT TOP 1 VersionID, Major, Minor, Revision, Patchnotes
FROM VERSIONS
ORDER BY Major DESC, Minor  DESC, Revision  DESC

Multiple sort columns can be specified. The sequence of the sort columns in the ORDER BY clause defines the organization of the sorted result set. The result set is sorted by the first column and then that ordered list is sorted by the second column, and so on.

Upvotes: 4

Related Questions