Home > Software design >  How to get column with max value for each key?
How to get column with max value for each key?

Time:09-04

I have a table :

CREATE TABLE ask (
    item TEXT ,
    value INTEGRER NOT NULL
);

INSERT INTO ask (item,value) VALUES
("A", 1),
("A", 4),
("A", 3),
("B", 0),
("B", 1),
("C", 2),
("C", 4);
item value
A 1
A 4
A 3
B 0
B 1
C 2
C 4

I want two additional columns with max and min values per item:

item value min_val max_val
A 1 1 4
A 4 1 4
A 3 1 4
B 0 0 1
B 1 0 1
C 2 2 4
C 4 2 4

How can I get that in SQLite? This doesn't work:

SELECT *, max(value) AS max_val, min(val) AS min_val FROM ask;

CodePudding user response:

Use window functions instead of aggregate functions:

SELECT *, 
       MIN(value) OVER (PARTITION BY item) min_val, 
       MAX(value) OVER (PARTITION BY item) max_val 
FROM ask;

See the demo.

CodePudding user response:

You should not do this. Most likely, you should keep your table as is and create a view instead:

CREATE VIEW ask_minmax AS (
    SELECT item,
           max(value) AS maxval,
           min(value) AS minval
    FROM ask
    GROUP BY item
);

CodePudding user response:

first of change text datatype to varchar in create table

CREATE TABLE ask (
item VARCHAR,
value Integer NOT NULL)

Insert Your Data

INSERT INTO ask (item,value)
VALUES
('A',   1),
('A',   4),
('A',   3),
('B',   0),
('B',   1),
('C',   2),
('C', 4);

Then after run my following query

With T1 As
(SELECT  item,max(value) as max_val, min(value) as min_val FROM ask
GROUP bY item)
Select T2.item,T2.value,T1.min_val,T1.max_val From ask T2
join T1 On T1.item = T2.item

CodePudding user response:

You can create a temporary table with the max/mins and then join it to create the desired table/result.

I.e.

select item, max(value) maxval, min(value) minval
from ask group by item;

then

SELECT a.item, a.value, maxval, minval
from 
  ask a
join
  (select item, max(value) maxval, min(value) minval from ask group by item) b
on a.item = b.item;
  • Related