How can I select different values in one table?-CodePudding

INST | MAXRATE | MAXDATE  | TAX |
--------------------------------
1    |   1.5   |  17      |  0  |     
2    |   0     |  20      |  0  |   
3    |   0     |  35      |  0  |   
4    |   0     |  45      |  18 |    
5    |   0     |  50      |  1  |

I have a table like this. And I want to select a maximum value in each row. For example, the first column has an INST and I want to pick the max value. And I also want to pick 50 as a MAXDATE. How can I pick these values? Here is an example output I want.

INST | MAXRATE | MAXDATE  | TAX |
---------------------------------
5    |   1.5   |  50      |  18 |

CodePudding user response：

CREATE TABLE t1 (
  INST NUMBER, MAXRATE NUMBER, MAXDATE NUMBER, TAX NUMBER
);

INSERT INTO t1 (INST, MAXRATE, MAXDATE, TAX) VALUES (1,  1.5, 17, 0);
INSERT INTO t1 (INST, MAXRATE, MAXDATE, TAX) VALUES (2,  0,   20, 0);
INSERT INTO t1 (INST, MAXRATE, MAXDATE, TAX) VALUES (3,  0,   35, 0);
INSERT INTO t1 (INST, MAXRATE, MAXDATE, TAX) VALUES (4,  0,   45, 18);
INSERT INTO t1 (INST, MAXRATE, MAXDATE, TAX) VALUES (5,  0,   50, 1);

SELECT MAX(INST), MAX(MAXRATE), MAX(MAXDATE), MAX(TAX) FROM t1;

https://sqlize.online/sql/oracle21/d281b1d5c7e91c8779773733c4f4273e/

CodePudding user response：

Could you try this idea or something similar?

SELECT MAX(INST) max_inst, b.max_maxrate
FROM YourTable a
INNER JOIN (
    SELECT inst, MAX(maxrate) max_maxrate
    FROM YourTable
    GROUP BY inst
) b 
ON a.inst = b.inst AND a.maxrate = b.maxrate