Home » SQL & PL/SQL » SQL & PL/SQL » doubt in a group by query (sqlplus-Oracle 9i)
doubt in a group by query [message #331084] Wed, 02 July 2008 05:10 Go to next message
abhishekaditya21
Messages: 1
Registered: July 2008
Junior Member
I have a query for which I have a table test as follows.


I need to write a query that gets me the starttime,ports,and count.
there should be a row for each of the unique 'count' available in the table, The row with the maximum starttime should be returned, along with the corresponding ports.


Please open the attachments to view the table structure.
THanks and regards
Abhishek

[Updated on: Wed, 02 July 2008 05:17]

Report message to a moderator

Re: doubt in a group by query [message #331107 is a reply to message #331084] Wed, 02 July 2008 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't attach a file, post it in the topic but before read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: doubt in a group by query [message #331110 is a reply to message #331084] Wed, 02 July 2008 05:49 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
use group by on count and max on the rest...
Re: doubt in a group by query [message #331114 is a reply to message #331110] Wed, 02 July 2008 05:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The OP wants the ports for the row with the maximum value of start time.

This will require either the MAX Analytic function or a join to another copy of the same table - one copy of the table is used to determine the maximum values, the other copy is used to get the rows corresponding to these values.
Re: doubt in a group by query [message #331124 is a reply to message #331114] Wed, 02 July 2008 05:58 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
I thought this is what he wanted.

SQL> select count, max(ports) PORTS, to_char(max(startime),'mm/dd/yyyy hh:mm:ss'
) TIME from test3 group by count;

     COUNT      PORTS TIME
---------- ---------- -------------------
       100          4 07/02/2008 03:07:09
       102          2 07/02/2008 03:07:41
       101          5 07/02/2008 03:07:29


Re: doubt in a group by query [message #331137 is a reply to message #331124] Wed, 02 July 2008 06:08 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What he asks for is
Quote:
The row with the maximum starttime should be returned, along with the corresponding ports.


What I reckon he wants is:
CNT	MAX_START_TIME	        MAX_PORT
101	25-05-2008 23:27:03	2
100	25-05-2008 23:26:46	3
102	25-05-2008 23:51:55	2
Previous Topic: Array Searching Logic
Next Topic: add the unconsildate amount to match with consolidate amount
Goto Forum:
  


Current Time: Tue Dec 06 00:12:34 CST 2016

Total time taken to generate the page: 0.09367 seconds