ChesterJohn ChesterJohn - 1 month ago 7
SQL Question

INNER JOIN with MAX Function

Help I'm new to programming I need to get the highest version data of each EXE names. I found same articles about this, but we have different table structures so they didn't really help me on my problem.

I have this tables:


table mForm


FormID FormName EXEname CurrentVersion
--------------------------------------------------------
BTC-01 CASH REVIEW BTC-01.EXE 1.0.2.5
BTC-02 CASH REQUEST BTC-02.EXE 1.1.2.4
BTC-03 PAYMENTS BTC-03.EXE 1.0.0.3



table mVersionHistory


EXEname FormName Version ReleasedDate ReleaseDescription
-----------------------------------------------------------------------------
BTC-01.EXE CASH REVIEW 1.0.2.5 08-08-16 IT REQ 10063
BTC-01.EXE CASH REVIEW 1.0.2.4 08-08-10 IT REQ 10051
BTC-01.EXE CASH REVIEW 1.0.2.3 08-08-09 IT REQ 10050
BTC-02.EXE CASH REQUEST 1.1.2.4 08-08-13 IT REQ 10003
BTC-02.EXE CASH REQUEST 1.1.2.0 08-08-10 IT REQ 10002
BTC-03.EXE PAYMENTS 1.0.0.2 08-08-07 IT REQ 10102
BTC-03.EXE PAYMENTS 1.0.0.1 08-08-06 IT REQ 10092



I want to return distinct data with the highest Versions.
like this:


EXEname FormName CurrentVersion Version ReleasedDate ReleaseDescription
----------------------------------------------------------------------------------------------
BTC-01.EXE CASH REVIEW 1.0.2.5 1.0.2.5 08-08-16 IT REQ 10063
BTC-02.EXE CASH REQUEST 1.1.2.4 1.1.2.4 08-08-13 IT REQ 10003
BTC-03.EXE PAYMENTS 1.0.0.3 1.0.0.2 08-08-07 IT REQ 10102


I can do it with my code but the problem is, I cant add the Description and ReleaseDate columns, every time I add them, all data would show up since they have different data in Description and ReleaseDate Columns.

SELECT
A.FormID [FORM ID]
, A.FormName [FORM NAME]
, A.ExeName [EXE NAME]
, A.CurrentVersion [CURRENT VERSION]
, B.RVersion AS [RELEASED VERSION]
FROM
mForm A
INNER JOIN
(SELECT Exename, MAX(Version) AS RVersion
FROM mVersionHistory
GROUP BY ExeName) B
ON A.ExeName = B.ExeName
GROUP BY A.FormID, B.FormName, A.CurrentVersion, B.RVersion, A.ExeName




Where should I add the Description and ReleaseDate columns?
Thanks

Answer

You can use ROW_NUMBER window function

;WITH cte 
     AS (SELECT A.formid                   [FORM ID], 
                A.formname                 [FORM NAME], 
                A.exename                  [EXE NAME], 
                A.currentversion           [CURRENT VERSION], 
                B.rversion                 AS [RELEASED VERSION], 
                ---You can add whatever columns need from mversionhistory table
                Row_number()OVER(partition BY A.exename ORDER BY b.version DESC) AS Rn 
         FROM   mform A 
                INNER JOIN mversionhistory B 
                        ON A.exename = B.exename) 
SELECT * 
FROM   cte 
WHERE  rn = 1