Identify Max Value if exist in group - Oracle SQL

Question

I'm Trying Identify max value in group.

I was trying with Max and Min Function. But i can't get max, only if there is a greater one, if they are all the same, I do not want to do anything.

Expected Result: Example 1

OWNER ACCOUNT MONEY isMAX Admin account1 1000 YES Admin account2 500 NO Admin account3 300 NO

Expected Result: Example 2

OWNER ACCOUNT MONEY isMAX Admin account1 500 Admin account2 500 Admin account3 500

Expected Result: Example 3

OWNER ACCOUNT MONEY isMAX Admin account1 500 YES Admin account2 500 YES Admin account3 300 NO

I'm playing with MAX, MIN and CASE function.

Gordon Linoff · Accepted Answer · 2017-11-01 23:09:54Z

1

Something like this:

select t.*, (case when min(money) over (partition by owner, account) = max(money) over (partition by owner, account) then NULL when money = max(money) over (partition by owner, account) then 'YES' else 'NO' end) as isMax from t;

answered Nov 1, 2017 at 23:09

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Blag Over a year ago

Small question Gordon, do you know the perf difference between over partition by and a simple join max group by ? I'm curious as I usually go straight on join with sub query and forgot a bit about over partition feature...

Gordon Linoff Over a year ago

@Blag . . . In most databases, the window functions have better performance than join and group by.

Blag · Accepted Answer · 2017-11-01 23:37:21Z

This should do it :

SQL Fiddle

Query 1:

SELECT t1.OWNER, t1.money, CASE WHEN t1.money = t2.ma AND t1.money > T2.mi THEN 'YES' WHEN t1.money < T2.ma THEN 'NO' ELSE '' END AS isMAX FROM t t1 INNER JOIN ( SELECT OWNER, max(money) ma, min(money) mi FROM t GROUP BY OWNER ) t2 ON t1.OWNER = t2.OWNER order by owner, ismax desc

Results:

| OWNER | MONEY | ISMAX | |--------|-------|--------| | Admin1 | 1000 | YES | | Admin1 | 500 | NO | | Admin1 | 500 | NO | | Admin2 | 500 | (null) | | Admin2 | 500 | (null) | | Admin2 | 500 | (null) | | Admin3 | 500 | YES | | Admin3 | 500 | YES | | Admin3 | 300 | NO |

Collectives™ on Stack Overflow

Identify Max Value if exist in group - Oracle SQL

2 Answers 2

2 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Related