how to get column with max value for each key in sqlite-CodePudding

I have a table as follows:

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

Now, I want to add two additional columns with max and min values per item. Demand output:

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

Can you please advice how can I get that output with sqlite ? Many thanks in advance.

Tried something like this but it doesnt work:

SELCET *, max(value) as max_val, min(val) as min_val FROM ask

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;

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.