Analytic function with MIN

Analytics
Enthusiast

Analytic function with MIN

Hi Guys,

I want to pull the minimum of column D for the parition of columns A, B anc C. I also want to then pull the column E and show the unique set of records for A,B,C and E. Something like this:

SELECT A, B, C, E, 
MIN(D) OVER (PARTITION BY A, B, C ) AS D
FROM DB.TABLE 
GROUP BY A, B, C, E

But then it won't work as it want me to put column D in the GROUP BY but i can't do that as it will increase the number of records.
I've done this alternatively by first fetching the min(D) on proper partition then joining that result with same table and just appending column E to it.

I want to know if there is any way I can do it in the single query as above?

 

Thanks in advance

4 REPLIES
Teradata Employee

Re: Analytic function with MIN

Since MIN(D) OVER (PARTITION BY A, B, C )  is unique to a set of values for A,B,C, Grouping By A,B,C,E,MIN(D) OVER (PARTITION BY A, B, C ) would be the same as grouping by A,B,C,E.  So why not just take the parser's advice and try that?

Highlighted
Junior Contributor

Re: Analytic function with MIN

You need to use QUALIFY to filter the rows with the minimum value and the apply DISTINCT:

 

 

SELECT DISTINCT A, B, C, E 
FROM DB.TABLE
QUALIFY  
   MIN(D) OVER (PARTITION BY A, B, C) = D

or

SELECT DISTINCT A, B, C, E
FROM DB.TABLE
QUALIFY  
  RANK() OVER (PARTITION BY A, B, C
      ORDER BY D) = 1

 

 

Enthusiast

Re: Analytic function with MIN

First of all I am big fan of you Dnoeth...!!

 

Coming to the question, may be I did not clearly mentioned that I need all the possible values of col E against the unique combination of A, B and C. But want the lowest (col D) populated against all the rows.

 

Sample Data:

A	B	        C	 D	                 E
1	10/31/2016	X	11/29/2016	11/23/2016 3:18
1	10/31/2016	X	12/9/2016	12/2/2016 5:00
1	10/31/2016	X	2/20/2017	2/17/2017 11:50

 Expected Output: (I achieved this by a query using derived table but looking for an alternative way)

A	B	        C	D	                  E
1	10/31/2016	X	11/29/2016	11/23/2016 3:18
1	10/31/2016	X	12/9/2016	11/23/2016 3:18
1	10/31/2016	X	2/20/2017	11/23/2016 3:18

Dieter's Query Output:

A	B	        C          D	            E
1	10/31/2016	X	11/29/2016	11/23/2016 3:18

 

 

Enthusiast

Re: Analytic function with MIN

Oh Please ognore the above post .

 

My actual query gave the desired result by just applying DISTINCT and grouping by col D as suggested by optimizer.

SELECT DISTINCT A, B, C, E, 
MIN(D) OVER (PARTITION BY A, B, C ) AS MIN_D
FROM DB.TABLE 
GROUP BY A, B, C, D, E

Again thanks Dnoeth and CJColeman for your inputs.