I have two tables about users where I want to join two tables and show the latest remarks of each user
bellow is table
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;
Please give any solution to query 1 remark per user
(Note: sorry I cannot alter column details if any)
Live link Oracle Live
Best Answer
What you want to do is something like this with ROW_NUMBER() and
PARTITION
ing by cust_id andORDER
ingBY 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 simultaneousremarks
by the same customer at the same time.Create and populate customers table:
Do the same for customer_review:
Then run the following SQL:
Result:
(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 liketo extract the
INTEGER
from thereview_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
contact_date
is commented out. You then just callTO_DATE(contact_time)
when you just want to get the date (example shown in the fiddle - tablecustomer_review_bis
).GENERATED AS
(akaCOMPUTED BY
orCALCULATED
) column. These are very convenient and once defined, you have an effective guarantee that the date will always match the value incontact_time
. This functionality used to be performed byTRIGGER
s - it really is worth learning about these. The syntaxcolumn_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
):If you choose the
GENERATED AS
, you have to slightly modify theINSERT
statement by adding the fields that are updatable:Finally, as @EdStevens says, Oracle 10 is EOL (End of Life) - you really should think about upgrading!