当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - 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!

网友答案:

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 TOP):

SELECT *
FROM Version
ORDER BY Major DESC, Minor  DESC, Revision  DESC 
  OFFSET 0 ROWS
  FETCH FIRST 1 ROW ONLY ;
网友答案:
SELECT TOP 1 * FROM MyTable
WHERE Major<[email protected] AND Minor<[email protected] AND Revision<@REVISION
ORDER BY Major DESC, Minor DESC, Revision DESC
分享给朋友:
您可能感兴趣的文章:
随机阅读: