SQL Analytical Function: Teradata Vs. MS SQL syntax

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.

Analytical function: ROW_NUMBER () with Qualify in Teradata

This post will help to understand QUALIFY  ROW_NUMBER OVER (PARTITION BY ...) phrase with simple example.

We take an example like below:

INDUST_CODE	INDUSTRY_NAME	ACTIVATE_DATE
1011	GOOGLE	13-12-2009
1013	YOUTUBE	01-09-2008
1011	GOOGLE	15-12-2009
1012	MICROSOFT	14-10-1999
1011	GOOGLE	11-12-2009

The data listed above have more than one row with Industry named ‘Google’.  Business needs the industry name without duplication, they don’t want one industry name more than once. They like to see the data like below:

INDUST_CODE	INDUSTRY_NAME
1011	GOOGLE
1012	MICROSOFT
1013	YOUTUBE

How do you do that?

You are expert in SQL, so wrote the script like below which works fine:

select INDUST_CODE,INDUSTRY_NAME

from INDUSTRY

QUALIFY

row_number() Over (partition by INDUST_CODE order by ACTIVATE_DATE desc)=1

If you want to know how the code worked? Then please find the explanation step by step:

1) Group by (Partition by)with Industry Code (INDUST_CODE)

2) Order by with ACTIVATE_DATE which means latest date will be used to find the first row

3) Then uses ‘QUALIFY’ to select the row.

Lets explain the syntax :

Make the translation easy:

qualify row_number() over (Partition and order by brace) = 1

    which means,

    Selecting first row from the result set after applying group by and order by function.