A few years back, I posted about Teradata analytical function where I described how this works and why we need to use analytical function : https://pauldhip.blogspot.com/2014/12/analytical-function-rownumber-with.html
However, this post includes SQL syntax for MS SQL and how it's different from Teradata.
The SQL Code I have written in Teradata in my earlier post to find latest version of the data by using below:
select INDUST_CODE,INDUSTRY_NAME
from INDUSTRY
QUALIFY
row_number() Over (partition by INDUST_CODE order by ACTIVATE_DATE desc)=1
Do you think, the above syntax will work in MS SQL? Not really!! MS SQL doesn't have QUALIFY function. You need to write the syntax a little different way and a few extra lines required to achieve same results.
Let's translate the above syntax for MS SQL:
SELECT INDUST_CODE, INDUSTRY_NAME, RANKRESULT
FROM(
SELECT INDUST_CODE, INDUSTRY_NAME ,
RANK() OVER (PARTITION BY INDUST_CODE ORDER BY ACTIVATE_DATE DESC) AS RANKRESULT
FROM INDUSTRY
) TT
WHERE TT.RANKRESULT=1
As you see, for MS SQL we had to use sub query and RANK function to achieve the same result we got in Teradata.
