0

I have two tables about users where I want to join two tables and show the latest remarks of each user

bellow is table

enter image description here

I want to query the latest remarks for each user (1 remark per user) bellow query I have written but it returns multiple remarks per user if date and time is the same for remarks

SELECT a.cust_id,b.remarks,b.contact_date,b.contact_time FROM customers a,(select * from customer_review
where (cust_id,contact_time,contact_date) in (select cust_id, max(contact_time),max(contact_date) from customer_review group by cust_id)
) b WHERE  a.cust_id=b.cust_id;

enter image description here

Please give any solution to query 1 remark per user (Note: sorry I cannot alter column details if any) Live link Oracle Live

  • 1
    If the date & time is the same, which of the "same" ones is actually the "latest"? – Philᵀᴹ Nov 4 at 10:09
  • @Philᵀᴹ That is the problem ... I tried to take based on rownum or any one review based on insert order.. if date and time is same – Santhoshkumar Sivaji Nov 4 at 10:47
  • A couple of other side observations. It appears that CONTACT_DATE is a DATE and CONTACT_TIME is a TIMESTAMP. A DATE also includes time, to the second, and TIMESTAMP includes date and time, to the microsecond. That makes CONTACT_DATE superfluous, as all of the information there is also in CONTACT_TIME. Also, Oracle 10 has long, long been out of support. Well past time to upgrade. – EdStevens Nov 4 at 13:47
0

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!

  • I managed to get the Oracle fiddle working (microsecond) and also I've proposed two solutions to the issue of having a superfluous contact_date column. – Vérace Nov 4 at 13:54
  • Final revision - with the correct answers this time! Thanks to @a_horse_with_no_name for pointing out my mistake - I think I got side-tracked with the replacement of the contact_date field... Ah well, these things happen! ;-) – Vérace Nov 4 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.