Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE customers
(
  cust_id INTEGER NOT NULL,
  cust_name VARCHAR (25) NOT NULL
);

INSERT INTO customers VALUES (1234, 'John Doe');
1 rows affected

INSERT INTO customers VALUES (1235, 'Ram'); 
1 rows affected

INSERT INTO customers VALUES (1236, 'Rahim');
1 rows affected

SELECT * FROM customers;


CUST_ID
CUST_NAME


1234
John Doe

1235
Ram

1236
Rahim


CREATE TABLE customer_review
(
  review_id VARCHAR (25) NOT NULL,
  cust_id   INTEGER NOT NULL,
  remarks   VARCHAR (250) NOT NULL,
  contact_date DATE NOT NULL,
  contact_time TIMESTAMP NOT NULL
);
SELECT CURRENT_TIMESTAMP FROM dual;


CURRENT_TIMESTAMP


15-FEB-21 10.13.19.507972 AM +00:00


INSERT INTO customer_review VALUES 
('NEW123', 1235, 'I am new user', 
TO_DATE('2019-10-27', 'yyyy-mm-dd'), TO_TIMESTAMP('2019-10-27 06:10:13.278456',  'yyyy-mm-dd hh24:mi:ss.ff6'));
1 rows affected

INSERT INTO customer_review VALUES 
('CUST123', 1234, 'This is awesome product', 
TO_DATE('2019-10-26', 'yyyy-mm-dd'), TO_TIMESTAMP('2019-10-26 11:15:15.123987', 'yyyy-mm-dd hh24:mi:ss.ff6'));
1 rows affected

INSERT INTO customer_review VALUES 
('CUST124', 1234, 'This is not good product. I have changed my mind now', 
TO_DATE('2019-10-27', 'yyyy-mm-dd'), TO_TIMESTAMP('2019-10-27 16:25:15.567891', 'yyyy-mm-dd hh24:mi:ss.ff6'));
1 rows affected

INSERT INTO customer_review VALUES 
('CUST125', 1234, 'After upgrade, it is now working fine!', 
TO_DATE('2019-10-27', 'yyyy-mm-dd'), TO_TIMESTAMP('2019-10-27 19:52:56.345345', 'yyyy-mm-dd hh24:mi:ss.ff6'));
1 rows affected

SELECT * FROM customer_review
ORDER BY cust_id ASC, contact_time DESC;


REVIEW_ID
CUST_ID
REMARKS
CONTACT_DATE
CONTACT_TIME


CUST125
1234
After upgrade, it is now working fine!
27-OCT-19
27-OCT-19 07.52.56.345345 PM

CUST124
1234
This is not good product. I have changed my mind now
27-OCT-19
27-OCT-19 04.25.15.567891 PM

CUST123
1234
This is awesome product
26-OCT-19
26-OCT-19 11.15.15.123987 AM

NEW123
1235
I am new user
27-OCT-19
27-OCT-19 06.10.13.278456 AM


SELECT tab.cust_id, tab.remarks, c.cust_name
FROM
(
  SELECT 
   review_id, 
   cust_id,
   ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY cust_id ASC, contact_time DESC) rn, 
   remarks, 
   contact_date, 
   contact_time
  FROM customer_review
) tab
JOIN customers c ON tab.cust_id = c.cust_id
WHERE tab.rn = 1
ORDER BY tab.cust_id;


CUST_ID
REMARKS
CUST_NAME


1234
After upgrade, it is now working fine!
John Doe

1235
I am new user
Ram


SELECT CAST(SUBSTR('CUST1235',  5, LENGTH('CUST1235')) AS SMALLINT) AS the_number FROM dual;


THE_NUMBER


1235


CREATE TABLE customer_review_bis
(
  review_id VARCHAR (25) NOT NULL,
  cust_id   INTEGER NOT NULL,
  remarks   VARCHAR (250) NOT NULL,
  -- contact_date DATE NOT NULL,
  contact_time TIMESTAMP NOT NULL
);
INSERT INTO customer_review_bis VALUES 
('NEW123', 1235, 'I am new user', 
TO_TIMESTAMP('2019-10-27 06:10:13.278456',  'yyyy-mm-dd hh24:mi:ss.ff6'));
1 rows affected

INSERT INTO customer_review_bis VALUES 
('CUST123', 1234, 'This is awesome product', 
TO_TIMESTAMP('2019-10-26 11:15:15.123987', 'yyyy-mm-dd hh24:mi:ss.ff6'));
1 rows affected

INSERT INTO customer_review_bis VALUES 
('CUST124', 1234, 'This is not good product. I have changed my mind now', 
TO_TIMESTAMP('2019-10-27 16:25:15.567891', 'yyyy-mm-dd hh24:mi:ss.ff6'));
1 rows affected

INSERT INTO customer_review_bis VALUES 
('CUST125', 1234, 'After upgrade, it is now working fine!', 
TO_TIMESTAMP('2019-10-27 19:52:56.345345', 'yyyy-mm-dd hh24:mi:ss.ff6'));
1 rows affected

SELECT tab.cust_id, tab.remarks, c.cust_name
FROM
(
  SELECT 
   review_id, 
   cust_id,
   ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY cust_id ASC, contact_time DESC) rn, 
   remarks, 
   TO_DATE(contact_time), 
   contact_time
  FROM customer_review_bis
) tab
JOIN customers c ON tab.cust_id = c.cust_id
WHERE tab.rn = 1
ORDER BY tab.cust_id;


CUST_ID
REMARKS
CUST_NAME


1234
After upgrade, it is now working fine!
John Doe

1235
I am new user
Ram


-- https://oracle-base.com/articles/11g/virtual-columns-11gr1
-- column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]

CREATE TABLE customer_review_ter
(
  review_id VARCHAR (25) NOT NULL,
  cust_id   INTEGER NOT NULL,
  remarks   VARCHAR (250) NOT NULL,
  contact_time TIMESTAMP NOT NULL,
  contact_date DATE GENERATED ALWAYS AS (TO_DATE(contact_time)) VIRTUAL
);
INSERT INTO customer_review_ter 
(review_id, cust_id, remarks, contact_time)
VALUES 
('NEW123', 1235, 'I am new user', 
TO_TIMESTAMP('2019-10-27 06:10:13.278456',  'yyyy-mm-dd hh24:mi:ss.ff6'));
1 rows affected

INSERT INTO customer_review_ter 
(review_id, cust_id, remarks, contact_time)
VALUES 
('CUST123', 1234, 'This is awesome product', 
TO_TIMESTAMP('2019-10-26 11:15:15.123987', 'yyyy-mm-dd hh24:mi:ss.ff6'));
1 rows affected

INSERT INTO customer_review_ter 
(review_id, cust_id, remarks, contact_time)
VALUES 
('CUST124', 1234, 'This is not good product. I have changed my mind now', 
TO_TIMESTAMP('2019-10-27 16:25:15.567891', 'yyyy-mm-dd hh24:mi:ss.ff6'));
1 rows affected

INSERT INTO customer_review_ter 
(review_id, cust_id, remarks, contact_time)
VALUES 
('CUST125', 1234, 'After upgrade, it is now working fine!', 
TO_TIMESTAMP('2019-10-27 19:52:56.345345', 'yyyy-mm-dd hh24:mi:ss.ff6'));
1 rows affected

SELECT tab.cust_id, tab.remarks, c.cust_name
FROM
(
  SELECT 
   review_id, 
   cust_id,
   ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY cust_id ASC, contact_time DESC) rn, 
   remarks, 
   contact_time, 
   contact_date
  FROM customer_review_ter
) tab
JOIN customers c ON tab.cust_id = c.cust_id
WHERE tab.rn = 1
ORDER BY tab.cust_id;


CUST_ID
REMARKS
CUST_NAME


1234
After upgrade, it is now working fine!
John Doe

1235
I am new user
Ram
REVIEW_ID	CUST_ID	REMARKS	CONTACT_DATE	CONTACT_TIME
CUST125	1234	After upgrade, it is now working fine!	27-OCT-19	27-OCT-19 07.52.56.345345 PM
CUST124	1234	This is not good product. I have changed my mind now	27-OCT-19	27-OCT-19 04.25.15.567891 PM
CUST123	1234	This is awesome product	26-OCT-19	26-OCT-19 11.15.15.123987 AM
NEW123	1235	I am new user	27-OCT-19	27-OCT-19 06.10.13.278456 AM
CUST_ID	REMARKS	CUST_NAME
1234	After upgrade, it is now working fine!	John Doe
1235	I am new user	Ram