ORDER BY In Aggregate Functions

-- this syntax is used for testing the ORDER BY clause in SAP HANA Aggregate Functions

CREATE COLUMN TABLE DEVTEST.MYEMPLOYEES 
(
EMPID INTEGER,
EMP VARCHAR(20),
ROLE VARCHAR(20),
SALARY INTEGER
);

INSERT INTO DEVTEST.MYEMPLOYEES VALUES(1,'Christiano','Manager',500000);
INSERT INTO DEVTEST.MYEMPLOYEES VALUES(2,'Eric','Manager',350000);
INSERT INTO DEVTEST.MYEMPLOYEES VALUES(3,'Ryan','Director',450000);
INSERT INTO DEVTEST.MYEMPLOYEES VALUES(4,'Bryan','Director',155000);
INSERT INTO DEVTEST.MYEMPLOYEES VALUES(5,'Doreen','Manager',510000);
INSERT INTO DEVTEST.MYEMPLOYEES VALUES(6,'Gabriel','Manager',355000);
INSERT INTO DEVTEST.MYEMPLOYEES VALUES(7,'Angel','Director',450000);
INSERT INTO DEVTEST.MYEMPLOYEES VALUES(8,'Robin','Director',150000);
INSERT INTO DEVTEST.MYEMPLOYEES VALUES(9,'Sarah','Manager',520000);
INSERT INTO DEVTEST.MYEMPLOYEES VALUES(10,'Peter','Manager',350000);

SELECT 
	'ORIG ORDER' AS ORDER_BY,
	STRING_AGG(EMP, '|') AS EMP_NAMES,  
	STRING_AGG(SALARY, '|') AS SALARIES
FROM "DEVTEST"."MYEMPLOYEES" 
UNION
SELECT 
	'EMP NAME ASC' AS ORDER_BY,
	STRING_AGG(EMP, '|' ORDER BY EMP) AS EMP_NAMES,  
	STRING_AGG(SALARY, '|' ORDER BY EMP) AS SALARIES
FROM "DEVTEST"."MYEMPLOYEES" 
UNION
SELECT 
	'SALARY DESC' AS ORDER_BY,
	STRING_AGG(EMP, '|' ORDER BY SALARY DESC) AS EMP_NAMES,  
	STRING_AGG(SALARY, '|' ORDER BY  SALARY DESC) AS SALARIES
FROM "DEVTEST"."MYEMPLOYEES";

SELECT 
	NTH_VALUE(EMP||'@'||SALARY, 2 ORDER BY SALARY DESC) AS SALARY_SECOND,
	NTH_VALUE(EMP||'@'||SALARY, 2 ORDER BY SALARY ASC) AS SALARY_PENULTIMATE
FROM "DEVTEST"."MYEMPLOYEES";

-- currently there is support for ORDER BY in these Aggregate Functions
-- 		AUTO_CORR
--		CORR
--		CORR_SPEARMAN
--		CROSS_CORR
--		DFT
--		FIRST_VALUE
--		LAST_VALUE
--		MEDIAN
--		NTH_VALUE
--		STRING_AGG