2017  Kodetalk | Feedback | Privacy Policy | Terms | About
userimage

Project data by combining multiple column in sorted order

I have some set of result which contain two columns related price i.e, ACTUAL_PRICE and ADJUSTMENT_PRICE. And my requirement is to combine both the columns and form a new column as FINAL_PRICE in a sorting order as ascending or descending.

Anyhow I am able to combine this two columns but unable to sort them properly. So the question is how to sort multiple columns at a time in a sorted manner?


Below sample queries will be useful for troubleshot the requirement.

CREATE TABLE ITEM_PRICE(

ITEM_ID INTEGER,

ACTUAL_PRICE DOUBLE,

ADJUSTMENT_PRICE DOUBLE,

USER_ID VARCHAR(50)

);


INSERT INTO ITEM_PRICE(ITEM_ID,ACTUAL_PRICE) VALUES(1,10.05);

INSERT INTO ITEM_PRICE(ITEM_ID,ACTUAL_PRICE) VALUES(2,15.05);

INSERT INTO ITEM_PRICE(ITEM_ID,ACTUAL_PRICE) VALUES(3,12.05);

INSERT INTO ITEM_PRICE(ITEM_ID,ACTUAL_PRICE) VALUES(4,18.05);

INSERT INTO ITEM_PRICE(ITEM_ID,ACTUAL_PRICE) VALUES(5,12.05);

INSERT INTO ITEM_PRICE(ITEM_ID,ACTUAL_PRICE) VALUES(6,13.05);


UPDATE ITEM_PRICE SET ADJUSTMENT_PRICE=9.70,USER_ID=2131 WHERE ITEM_ID=2;

UPDATE ITEM_PRICE SET ADJUSTMENT_PRICE=9.70,USER_ID=2101 WHERE ITEM_ID=5;


Thanks in advance for your time to look into my requirement.

userimage

You can use the sql function COALESCE which returns the first non-null expr in the expression list.


Solution for your exact problem.

SELECT ITEM_ID,ACTUAL_PRICE,ADJUSTMENT_PRICE,USER_ID,COALESCE(ADJUSTMENT_PRICE,ACTUAL_PRICE) FINAL_PRICE  FROM ITEM_PRICE ORDER BY COALESCE(ADJUSTMENT_PRICE,ACTUAL_PRICE)  ASC;


To know more about the above used function COALESCE (expr1, expr2, ..., exprn) visit https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions023.htm

Answer is