What you want to do is something like this with ROW_NUMBER() and PARTITIONing by cust_id and ORDERing BY contact_time DESC so that the first one will be the latest. A fiddle is available here - Oracle has microsecond precision, so it's very unlikely that you'll get simultaneous remarks by the same customer at the same time.
Create and populate customers table:
CREATE TABLE customers
(
cust_id INTEGER NOT NULL,
cust_name VARCHAR (25) NOT NULL
);
INSERT INTO customers VALUES (1234, 'John Doe');
INSERT INTO customers VALUES (1235, 'Ram');
INSERT INTO customers VALUES (1236, 'Rahim');
Do the same for customer_review:
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
);
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.278', 'yyyy-mm-dd hh24:mi:ss:ms'));
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.123', 'yyyy-mm-dd hh24:mi:ss:ms'));
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.567', 'yyyy-mm-dd hh24:mi:ss:ms'));
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.345', 'yyyy-mm-dd hh24:mi:ss:ms'));
Then run the following SQL:
SELECT c.cust_name, t.cust_id, t.remarks FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY cust_id ASC, contact_time DESC) AS rn,
*
FROM customer_review
) t
JOIN customers c
ON t.cust_id = c.cust_id
WHERE t.rn = 1;
Result:
CUST_ID REMARKS CUST_NAME
1234 After upgrade, it is now working fine! John Doe
1235 I am new user Ram
(after edit - these are now the correct answers - the remark by John Doe is his latest one as should be the case! Ram has only made one remark so by definition, it is the latest).
A few words of advice. You have your customers table with a plural and customer_review in the singular - pick one or the other and stick to it (you can search for "SQL style guide" and choose one you like. Personally, I recommend singular - a table is a set of things and therefore singular.
Next time you post, could you please include your table definitions as DDL text (CREATE TABLE xxxx (field_1 type_1...);) and your data as DML text (INSERT INTO xxxx VALUES (val_1, val2...);). Here are a few reasons why posting screenshots is not the best approach for questions on DBA.SE. There are a few articles on how to ask questions here on my profile - you might want to take a look?
Finally, I recommend that you use an integer for review_id - it makes sorting easier - otherwise you might be faced with having to use messy code like
SELECT CAST(SUBSTR('CUST1235', 5, LENGTH('CUST1235')) AS SMALLINT) AS no;
to extract the INTEGER from the review_id field.
It is possible to solve this problem using the Oracle ROWNUM pseudo-column, but this is non-standard and it's better to develop good habits - and it will make your application more portable in the event that you/your company decide to do this. I hope this has helped - if it doesn't correspond to your requirements, please let me know and we can try and modify it. p.s. welcome to the forum! :-)
Edit:
I finally got the Oracle fiddle working here - to microsecond precision.
Another point that struck me on rereading the question and my answer. You don't need to declared a contact_date field - it can be easily derived from the contact_time field. The data (date in this case) is therefore only stored in one field and one field only - which is conformant with RDBMS best practice.
There are two ways of doing this
- You can use the TO_DATE function when calling the table. Your table will now look like this (note
contact_date is commented out. You then just call TO_DATE(contact_time) when you just want to get the date (example shown in the fiddle - table customer_review_bis).
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
);
- You can also use a
GENERATED AS (aka COMPUTED BY or CALCULATED) column. These are very convenient and once defined, you have an effective guarantee that the date will always match the value in contact_time. This functionality used to be performed by TRIGGERs - it really is worth learning about these. The syntax column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL] is described here (oracle-base is a super site). Your table definition now becomes (also shown in fiddle - customer_review_ter):
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
); -- you just treat contact_date as a normal field when you call it, in
-- exactly the same way as you did in your original example - table
-- customer_review (first one) in the fiddle.
If you choose the GENERATED AS, you have to slightly modify the INSERTstatement by adding the fields that are updatable:
INSERT INTO customer_review_ter
(review_id, cust_id, remarks, contact_time)
VALUES (.... data for specified fields ....);
Finally, as @EdStevens says, Oracle 10 is EOL (End of Life) - you really should think about upgrading!
