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.
1 comment:
Thank you so much for providing information about REST, PUT and DELETE. REST APIs are actually very useful and provide some edge over other complex operations.
SQL Server Load Rest Api
Post a Comment