Show min value of duplicate row value as column value-CodePudding

I have a table called data with two columns, CODE and VALUE, and this rows:

CODE        VALUE1
2345        250
2345        50

How can i make to obtain the lowest value of duplicate CODE a virtual column called VALUE2?

CODE        VALUE1      VALUE2
2345        250         50

Thanks,

CodePudding user response：

Rather you can create a view in which derive the desired value with a window function through grouping by code such as

CREATE OR REPLACE VIEW v_tab AS 
SELECT t.*, MIN(value1) OVER (PARTITION BY code) AS value2
  FROM tab t --yourTable

CodePudding user response：

Using a cte to get minval per code

drop table if exists t;
create table t
(CODE int,        VALUE1 int);
insert into t values
(2345   ,     250),
(2345   ,     50);

with cte as 
(select code,min(value1) as value2 from t group by code)
select t.*,cte.value2
from t
join cte on cte.code = t.code and cte.value2 <> value1;

 ------ -------- -------- 
| CODE | VALUE1 | value2 |
 ------ -------- -------- 
| 2345 |    250 |     50 |
 ------ -------- -------- 
1 row in set (0.001 sec)

CodePudding user response：

If there are always two rows per CODE, you can consider VALUE1 to be the maximum value and VALUE2 to be the minimum:

SELECT CODE, MAX(VALUE1) AS VALUE1, MIN(VALUE1) AS VALUE2
  FROM mytable
 GROUP BY CODE;

Of course, this returns a single row even if there are more rows per CODE, but it's not clear from your question what exactly you're trying to achieve.