In this tutorial, you will learn, how to select unique records on one or more columns in Oracle. For example, a table has unique records, but one or more column values in the table are repetitive, and you want to pick the unique records based on those repetitive columns. Here is an example:
To demonstrate the example, I am creating a table below and will put some data into it.
Create a Table
The table stocks will record the daily share price updates. Column scrip_code is the share scrip code, scrip_name is the share name, and the price is its price updating every second. To store the current date and time using column curr_date which is set to default SYSDATE.
SET DEFINE OFF; CREATE TABLE STOCKS ( SCRIP_CODE INTEGER, SCRIP_NAME VARCHAR2(50 BYTE), PRICE NUMBER, CURR_DATE DATE DEFAULT sysdate ) /
Insert Data Script
SET DEFINE OFF;
Insert into STOCKS
(SCRIP_CODE, SCRIP_NAME, PRICE, CURR_DATE, SAME_SCRIP)
Values
(3, 'DCBBANK', 178, TO_DATE('03/19/2019 11:58:30', 'MM/DD/YYYY HH24:MI:SS'), 1);
Insert into STOCKS
(SCRIP_CODE, SCRIP_NAME, PRICE, CURR_DATE, SAME_SCRIP)
Values
(3, 'DCBBANK', 179, TO_DATE('03/19/2019 11:59:08', 'MM/DD/YYYY HH24:MI:SS'), 2);
Insert into STOCKS
(SCRIP_CODE, SCRIP_NAME, PRICE, CURR_DATE, SAME_SCRIP)
Values
(1, 'IGL', 290, TO_DATE('03/19/2019 11:59:15', 'MM/DD/YYYY HH24:MI:SS'), 1);
Insert into STOCKS
(SCRIP_CODE, SCRIP_NAME, PRICE, CURR_DATE, SAME_SCRIP)
Values
(1, 'IGL', 293, TO_DATE('03/19/2019 11:58:36', 'MM/DD/YYYY HH24:MI:SS'), 2);
Insert into STOCKS
(SCRIP_CODE, SCRIP_NAME, PRICE, CURR_DATE, SAME_SCRIP)
Values
(1, 'IGL', 292, TO_DATE('03/19/2019 11:58:03', 'MM/DD/YYYY HH24:MI:SS'), 3);
Insert into STOCKS
(SCRIP_CODE, SCRIP_NAME, PRICE, CURR_DATE, SAME_SCRIP)
Values
(2, 'VOLTAS', 577, TO_DATE('03/19/2019 11:59:23', 'MM/DD/YYYY HH24:MI:SS'), 1);
Insert into STOCKS
(SCRIP_CODE, SCRIP_NAME, PRICE, CURR_DATE, SAME_SCRIP)
Values
(2, 'VOLTAS', 580, TO_DATE('03/19/2019 11:59:30', 'MM/DD/YYYY HH24:MI:SS'), 2);
Insert into STOCKS
(SCRIP_CODE, SCRIP_NAME, PRICE, CURR_DATE, SAME_SCRIP)
Values
(2, 'VOLTAS', 565, TO_DATE('03/19/2019 11:58:23', 'MM/DD/YYYY HH24:MI:SS'), 3);
Insert into STOCKS
(SCRIP_CODE, SCRIP_NAME, PRICE, CURR_DATE, SAME_SCRIP)
Values
(2, 'VOLTAS', 600, TO_DATE('03/19/2019 11:58:43', 'MM/DD/YYYY HH24:MI:SS'), 4);
COMMIT;Have a Look at The Current Data
select * from stocks;
Select Unique Records on One or More Columns in Oracle
You can see in the above picture, that data is unique but scrip_code and scrip_name are replicated and for example, you require to pick the unique data on behalf of column scrip_name to see the most latest stock price only. To select the unique records based on share name (scrip_name) the following is the SQL query:
SELECT scrip_name,
price,
curr_date,
ROW_NUMBER ()
OVER (PARTITION BY scrip_name ORDER BY curr_date DESC)
latest_date
FROM stocks;Output (Still not unique. It will be in the next query.)
You can see, that data is not as per your requirement now, but I have added a one more column latest_date by using the analytic function ROW_NUMBER(). By using this function, I have assigned the number by scrip_name to every row in descending order of current date. Now we can easily select the unique data from the above data by taking the above query into the sub-query and by specifying the latest_date column is equal to 1. Below is the example of a full SQL query:
SELECT scrip_name share_name, price, latest_date as_of_date
FROM (SELECT scrip_name,
price,
curr_date,
ROW_NUMBER ()
OVER (PARTITION BY scrip_name ORDER BY curr_date DESC)
latest_date
FROM stocks)
WHERE latest_date = 1;Output
Note: The order by clause is mandatory in the ROW_NUMBER() function.
Suppose if I ask you to select the data from the stocks table and show me the opening price of all shares.
Then you can simply run the above SQL query by changing the descending order to ascending order of current date column. Below is the example:
SELECT scrip_name share_name, price, latest_date as_of_date
FROM (SELECT scrip_name,
price,
curr_date,
ROW_NUMBER ()
OVER (PARTITION BY scrip_name ORDER BY curr_date ASC)
latest_date
FROM stocks)
WHERE latest_date = 1;The SQL query to select unique data based on two columns.
The following query will select the scrip_code and scrip_name to show the same result as above. The difference is, we will add one more column in the query and the PARTITION BY clause. Here is an example:
SELECT scrip_code share_code,
scrip_name share_name,
price,
latest_date as_of_date
FROM (SELECT scrip_code,
scrip_name,
price,
curr_date,
ROW_NUMBER ()
OVER (PARTITION BY scrip_code, scrip_name
ORDER BY curr_date ASC)
latest_date
FROM stocks)
WHERE latest_date = 1;
