sccddlandquery.txtHi we run 2014 Enterprise. You can see my table in the attached. I'm often faced with getting a value from a record that contains the max value of another column. For instance when i want the ids from the table shown that are on the same records having max rowversionnumber grouped by Gid, i do things like what you see in the query below the ddl. Sometimes i use the rank function instead. But the queries always seem less than elegant. Is there an elegant way to do this kind of thing?
Answer by ThomasRushton ·
I would do something more like
WITH cte AS (SELECT ID, Gid, RowversionStamp,RowVersionNumber, RANK() OVER (partition by gid order by rowversionnumber desc) AS ranking)
SELECT * FROM cte where ranking = 1
(untested, but you should get the general idea)