CODEWITHSUNDEEP
sqloracle-database
White Mask Guy
White Mask Guy

Reputation: 67

Oracle SQL: getting all row maximum number from specific multiple criteria

I have the following table named foo:

ID | D1  | D2  | D3 |
---------------------
1 | 47  | 3   | 71 |
2 | 47  | 98  | 82 | 
3 | 0   | 99  | 3  |
4 | 3   | 100 | 6  |
5 | 48  | 10  | 3  |
6 | 49  | 12  | 4  |

I want to run a select query and have the results show like this

ID | D1  | D2  | D3 | Result |
------------------------------
 1 | 47  | 3   | 71 | D3     |
 2 | 47  | 98  | 82 | D2     | 
 3 | 0   | 99  | 3  | D2     |
 4 | 3   | 100 | 6  | D2     |
 5 | 48  | 10  | 3  | D1     |
 6 | 49  | 12  | 4  | D1     |

So, basically I want to get Maximum value between D1, D2, D3 column divided by id. As You may seen , ID 1 have D3 in the Result column since Maximum value between D1 : D2 : D3

That Means 4 : 3 : 71 , Max value is 71. Thats Why The Result show 'D3'

Is there a way to do this in a sql query ?

Thanks!

Upvotes: 0

Views: 98

Answers (2)

Strawberry
Strawberry

Reputation: 33935

Consider the following - a normalized approach...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL 
,d INT NOT NULL
,val INT NOT NULL
,PRIMARY KEY(id,d)
);

INSERT INTO my_table VALUES
(1,1,47),
(2,1,47),
(3,1,0),
(4,1,3),
(5,1,48),
(6,1,49),
(1,2,3),
(2,2,98),
(3,2,99),
(4,2,100),
(5,2,10),
(6,2,12),
(1,3,71),
(2,3,82),
(3,3,3),
(4,3,6),
(5,3,3),
(6,3,4);

SELECT * FROM my_table;
+----+---+-----+
| id | d | val |
+----+---+-----+
|  1 | 1 |  47 |
|  1 | 2 |   3 |
|  1 | 3 |  71 |
|  2 | 1 |  47 |
|  2 | 2 |  98 |
|  2 | 3 |  82 |
|  3 | 1 |   0 |
|  3 | 2 |  99 |
|  3 | 3 |   3 |
|  4 | 1 |   3 |
|  4 | 2 | 100 |
|  4 | 3 |   6 |
|  5 | 1 |  48 |
|  5 | 2 |  10 |
|  5 | 3 |   3 |
|  6 | 1 |  49 |
|  6 | 2 |  12 |
|  6 | 3 |   4 |
+----+---+-----+

SELECT x.* 
  FROM my_table x 
  JOIN  
     ( SELECT id,MAX(val) max_val FROM my_table GROUP BY id) y 
    ON y.id = x.id 
   AND y.max_val = x.val;
+----+---+-----+
| id | d | val |
+----+---+-----+
|  1 | 3 |  71 |
|  2 | 2 |  98 |
|  3 | 2 |  99 |
|  4 | 2 | 100 |
|  5 | 1 |  48 |
|  6 | 1 |  49 |
+----+---+-----+

(This is intended as a MySQL solution - I'm not familiar with ORACLE syntax, so apologies if this doesn't port)

Does this answer your comment?

SELECT x.* , y.max_val
  FROM my_table x
  JOIN
     ( SELECT id,MAX(val) max_val FROM my_table GROUP BY id) y
    ON y.id = x.id ;
+----+---+-----+---------+
| id | d | val | max_val |
+----+---+-----+---------+
|  1 | 1 |  47 |      71 |
|  1 | 2 |   3 |      71 |
|  1 | 3 |  71 |      71 |
|  2 | 1 |  47 |      98 |
|  2 | 2 |  98 |      98 |
|  2 | 3 |  82 |      98 |
|  3 | 1 |   0 |      99 |
|  3 | 2 |  99 |      99 |
|  3 | 3 |   3 |      99 |
|  4 | 1 |   3 |     100 |
|  4 | 2 | 100 |     100 |
|  4 | 3 |   6 |     100 |
|  5 | 1 |  48 |      48 |
|  5 | 2 |  10 |      48 |
|  5 | 3 |   3 |      48 |
|  6 | 1 |  49 |      49 |
|  6 | 2 |  12 |      49 |
|  6 | 3 |   4 |      49 |
+----+---+-----+---------+

Upvotes: 0

user3278460
user3278460

Reputation:

For Oracle please try this one

select foo.*, case when greatest(d1, d2, d3) = d1 then 'D1'
                   when greatest(d1, d2, d3) = d2 then 'D2'
                   when greatest(d1, d2, d3) = d3 then 'D3'
              end result
from foo

Upvotes: 3

Related Questions