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
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!
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.
The 2012 version has implemented the standard
OFFSET / FETCH syntax (in previous versions you'll have to use the proprietary
SELECT * FROM Version ORDER BY Major DESC, Minor DESC, Revision DESC OFFSET 0 ROWS FETCH FIRST 1 ROW ONLY ;