SQLize | PHPize | SQLtest

DROP TABLE if EXISTS public.coins; DROP TABLE if EXISTS ans; CREATE TABLE IF NOT EXISTS public.coins( dt VARCHAR(16), avg_price NUMERIC, symbol VARCHAR(8), high_price NUMERIC, vol NUMERIC, full_nm VARCHAR(128), tx_cnt NUMERIC ); INSERT INTO public.coins (dt, avg_price, symbol, high_price, vol, full_nm, tx_cnt) VALUES ('2018-10-20', 10, 'DOGE', 1, 10, 'WOW', 0); INSERT INTO public.coins (dt, avg_price, symbol, high_price, vol, full_nm, tx_cnt) VALUES ('2019-10-20', 10, 'DOGE', 2, 15, 'WOW', 1); INSERT INTO public.coins (dt, avg_price, symbol, high_price, vol, full_nm, tx_cnt) VALUES ('2018-10-10', 100, 'DOGE', 3, 0, 'NO', 1); INSERT INTO public.coins (dt, avg_price, symbol, high_price, vol, full_nm, tx_cnt) VALUES ('2018-09-10', 100, 'DOGE', 3, 0, 'NO', 1); INSERT INTO public.coins (dt, avg_price, symbol, high_price, vol, full_nm, tx_cnt) VALUES ('2018-08-10', 100, 'DOGE', 2, 0, 'NO', 1); INSERT INTO public.coins (dt, avg_price, symbol, high_price, vol, full_nm, tx_cnt) VALUES ('2018-10-30', 15, 'DOGE', 4, 25, 'yes', 2); SELECT symbol, dt FROM public.coins WHERE (symbol, high_price, dt) IN (SELECT symbol, MAX(high_price) as high_price, MIN(dt) as dt FROM public.coins GROUP BY symbol) ORDER BY high_price DESC, symbol ASC; /*SELECT UPPER(full_nm) AS full_name, min(dt) as dt, MAX(high_price) AS price FROM ( SELECT UPPER(full_nm) as full_nm, MIN(dt) as dt, high_price FROM public.coins GROUP BY UPPER(full_nm), high_price ) AS coins GROUP BY UPPER(full_nm);*/