doubt in a group by query [message #331084] |
Wed, 02 July 2008 05:10  |
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 #331114 is a reply to message #331110] |
Wed, 02 July 2008 05:53   |
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   |
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  |
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
|
|
|