Select in Sql Server

Question

Select in Sql Server

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

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

I did, but it did not work.

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

sql sql-server

asked by anonymous 18.05.2017 / 12:14

2 answers

How to include a PHP variable inside text saved in MySQl Error installing Netbeans 8.2 on Windows 10 [closed]

score 1 · Answer 1

The statement leaves open if, for the largest Version value, it should return all rows (if there is more than one) or if it should get the highest value for the Version.

Given 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;

score 0 · Answer 2

You have several alternatives to get the desired result. Here are three alternatives.

The first one using a "window function":

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