Analytics

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-19-2017
12:13 AM

04-19-2017
12:13 AM

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 4

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-19-2017
09:41 AM

04-19-2017
09:41 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-19-2017
12:30 PM

04-19-2017
12:30 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-20-2017
12:52 AM

04-20-2017
12:52 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-20-2017
01:19 AM

04-20-2017
01:19 AM

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.