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
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?
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
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.
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
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.