Select in Sql Server

Question

Good morning. I have a table with columns Version, Updated, LastChanged. I want to make a query to select the highest version value, with the respective LastChanged, corresponding to Updated = 1;

I did it like this, but it didn’t work.

select Max(Version), LastChanged from BMTDatabaseUpdate where Updated=1 

Answer 1

The statement leaves open whether, for the highest value of Version, it should return all lines (if there is more than one) or whether it should get the highest value for the pair (Version, Lastchanged).

Considering the second premise, evaluate the following code:

-- código #1
with cteSeq as (
SELECT Version, LastChanged,
       Seq= row_number() over(order by Version desc, LastChanged desc)
  from BMTDatabaseUpdate
   where Updated = 1
)
SELECT Version, LastChanged
  from cteSeq
  where Seq = 1;

Answer 2

There are several alternatives to get the desired result. Here are three alternatives.

The first using a "Function window":

SELECT *
  FROM
(
    SELECT Version, 
           LastChanged,
           ROW_NUMBER() OVER (ORDER BY Version DESC, LastChanged DESC) RN
      FROM BMTDatabaseUpdate  
     WHERE Updated = 1  
) V
WHERE RN = 1

The second using a sub-query:

SELECT BMT.Version,
       MAX(BMT.LastChanged) LastChanged,
       BMT.Updated
  FROM BMTDatabaseUpdate BMT
 INNER JOIN 
 (
     SELECT MAX(Version) MVersion
       FROM BMTDatabaseUpdate
      WHERE Updated = 1
 ) MaiorVersao
   ON MaiorVersao.MVersion= BMT.Version
 WHERE BMT.Updated = 1 
 GROUP BY BMT.Version