OraFAQ Forum: SQL & PL/SQL » SQL Query for Last Comment

Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query for Last Comment

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Query for Last Comment [message #260416]

Sun, 19 August 2007 11:47

lanebaker
Messages: 3
Registered: August 2007

Junior Member

Hi -

I'm looking for how to write a sql query to only put the last comment of a case into a report. Example - there are a number of comments in the case - but I only want to look at the latest comment. Please need some help here.
Thanks
Lane

Report message to a moderator

Re: SQL Query for Last Comment [message #260417 is a reply to message #260416]

Sun, 19 August 2007 11:57

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

No problem. If you know which comment is the last one.

If there was a timestamp column, you might try with something like

SELECT t.commnent
FROM table t
WHERE t.case = this_case
  AND t.timestamp_column = (SELECT max(t1.timestamp_column)
                            FROM table t1
                            WHERE t1.case = t.case
                           );

Report message to a moderator

Re: SQL Query for Last Comment [message #260418 is a reply to message #260416]

Sun, 19 August 2007 12:16

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I have a basket filled with balls.
Which ball is the "last" ball?

Report message to a moderator

Re: SQL Query for Last Comment [message #260424 is a reply to message #260417]

Sun, 19 August 2007 14:27

lanebaker
Messages: 3
Registered: August 2007

Junior Member

ok.. I just found out the field is actually a memo field. It is in the following format:

UserName <userinitials>,00/00/00: Various comments.

All users are various lengths...

Report message to a moderator

Re: SQL Query for Last Comment [message #260425 is a reply to message #260424]

Sun, 19 August 2007 15:08

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

A memo field? Before someone starts extracting data which might be interesting for you, are you sure it is Oracle you use? Aren't memos found in Access? Oracle doesn't support this data type.

Report message to a moderator