Home » SQL & PL/SQL » SQL & PL/SQL » SQL query - help needed (Oracle 9.2.0.8)
SQL query - help needed [message #309024] Wed, 26 March 2008 04:36 Go to next message
shahnazurs
Messages: 240
Registered: June 2005
Location: India
Senior Member
Hi,

I need to select the max time for the transaction using the below query. I’m able to get the o/p. Here the problem is I need to fetch the corrletorid (primary key) as well. if try to get that I’m getting the o/p like case2.

I want the o/p to be like case 1 with the corrleator id . please help me to get the o/p.

Case 1: select b.TransactionName, max(to_char(b.duration, 'mm/dd/yyyy hh24:mi:ss.ff')) as average from trandetails a,subtrandetails b where a.CORRELATORID=b.PCORRELATORID and b.PCORRELATORID='11' group by b.transactionname


TRANSACTIONNAME MAXTIME
------------------------- -----------------------------
FINT3 12/10/2007 19:53:09.042000
FINT 12/10/2007 19:31:07.042000
FINT2 12/10/2007 19:31:07.042000

SQL> /


Case 2: select b.TransactionName,b.correlatorid, max(to_char(b.duration, 'mm/dd/yyyy hh24:mi:ss.ff')) as average from trandetails a,subtrandetails b where a.CORRELATORID=b.PCORRELATORID and b.PCORRELATORID='11' group by b.transactionname,b.correlatorid
/

SQL> /

TRANSACTIONNAME CORRELATORID AVERAGE
------------------------- -------------------- -----------------------------
FINT2 102 12/10/2007 19:31:07.042000
FINT3 108 12/10/2007 19:53:09.042000
FINT3 103 12/10/2007 19:31:07.042000
FINT 101 12/10/2007 19:31:07.042000

in the above output i should have only record(max time) for FINT3.

Thanks
Re: SQL query - help needed [message #309027 is a reply to message #309024] Wed, 26 March 2008 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is NOT an expert question. Why did you post this in expert forum and not in newbie one?

It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide
Follow them including what is said about formatting.

Clue: Use analytic function.

Regards
Michel

[Updated on: Wed, 26 March 2008 04:44]

Report message to a moderator

Re: SQL query - help needed [message #309038 is a reply to message #309027] Wed, 26 March 2008 05:00 Go to previous message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Study analytical functions in oracle for this.
Quote:

http://www.akadia.com/services/ora_analytic_functions.html

Previous Topic: Trigger
Next Topic: To store PDF template in a oracle
Goto Forum:
  


Current Time: Wed Dec 07 09:01:14 CST 2016

Total time taken to generate the page: 1.71845 seconds