Home » SQL & PL/SQL » SQL & PL/SQL » Need help on sorting per column 1, then min/max from column 2 and corresponding column 3 (merged)
Need help on sorting per column 1, then min/max from column 2 and corresponding column 3 (merged) [message #340535] |
Wed, 13 August 2008 03:54  |
suhasbhide
Messages: 9 Registered: February 2008
|
Junior Member |
|
|
I have following data which I need to sort out per week and per month basis.
Host Date Size
Host1 01.08.2008 100
Host1 02.08.2008 120
Host1 03.08.2008 400
Host2 01.08.2008 120
Host2 02.08.2008 200
Host2 10.08.2008 400
The data should be sorted as Host v/s Min(size) and the respective date,Max(Size)
with respective date.
Using Min / Max, I can get the output from "Size" column but I cannot get the
corresponding date. Pls. help on this.
|
|
|
|
|
Re: Need help on sorting per column 1, then min/max from column 2 and corresponding column 3 [message #340705 is a reply to message #340535] |
Wed, 13 August 2008 20:36   |
suhasbhide
Messages: 9 Registered: February 2008
|
Junior Member |
|
|
Hi...
Here are the scripts for table creation and data.
create table test1 (
Hostname varchar2(10),
datproc date,
sum_gb number(10)
)
insert into test1 values ('Host1','01-Aug-2008',100);
insert into test1 values ('Host1','02-Aug-2008',120);
insert into test1 values ('Host1','03-Aug-2008',400);
insert into test1 values ('Host2','01-Aug-2008',120);
insert into test1 values ('Host2','02-Aug-2008',200);
insert into test1 values ('Host2','03-Aug-2008',400);
SQL> select * from test1;
HOSTNAME DATPROC SUM_GB
---------- ---------- ----------
Host1 01.08.2008 100
Host1 02.08.2008 120
Host1 03.08.2008 400
Host2 01.08.2008 120
Host2 02.08.2008 200
Host2 03.08.2008 400
6 rows selected.
Now I want to get max / avg and min values from sum_gb for each hostname, which I can get using.
SQL> select hostname,min(sum_gb),avg(sum_gb),max(sum_gb) from test1 group by hostname;
HOSTNAME MIN(SUM_GB) AVG(SUM_GB) MAX(SUM_GB)
---------- ----------- ----------- -----------
Host1 100 206.666667 400
Host2 120 240 400
------
Now my requirement is to have the date values alongwith the min / avg or max values and it should look something like below..
Will show only part of the output...
HOSTNAME MIN(SUM_GB) DATPROC MAX(SUM_GB) DATPROC
---------- ----------- ------------ ----------- -----------
Host1 100 01-AUG-2008 400 03-AUG-2008
Host2 120 01-AUG-2008 400 03-AUG-2008
Thus, I want to have MIN value per HOSTNAME and the next column will the date corresponding with the MIN value, same for MAX.
I have tried using GROUP BY, but cannot get the result.
Thanks.
|
|
|
|
Re: Need help on sorting per column 1, then min/max from column 2 and corresponding column 3 [message #340727 is a reply to message #340705] |
Thu, 14 August 2008 00:59   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
In addition, '01-Aug-2008' is NOT a date, it is a string:
SQL> create table test1 (
2 Hostname varchar2(10),
3 datproc date,
4 sum_gb number(10)
5 )
6 /
Table created.
SQL> insert into test1 values ('Host1','01-Aug-2008',100);
insert into test1 values ('Host1','01-Aug-2008',100)
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
Regards
Michel
|
|
|
Please explain KEEP & DENSE_RANK [message #340730 is a reply to message #340535] |
Thu, 14 August 2008 01:10   |
shiju_t_v
Messages: 56 Registered: May 2007 Location: kannur,kerala
|
Member |
|
|
I got this from a post , please explain
SCOTT@orcl_11g> SELECT hostname,
2 MIN (sum_gb),
3 MIN (datproc) KEEP (DENSE_RANK FIRST ORDER BY sum_gb) AS min_datproc,
4 MAX (sum_gb),
5 MAX (datproc) KEEP (DENSE_RANK LAST ORDER BY sum_gb) AS max_datproc
6 FROM test1
7 GROUP BY hostname
8 /
HOSTNAME MIN(SUM_GB) MIN_DATPROC MAX(SUM_GB) MAX_DATPROC
---------- ----------- ----------- ----------- -----------
Host1 100 01-Aug-2008 400 03-Aug-2008
Host2 120 01-Aug-2008 400 03-Aug-2008
SCOTT@orcl_11g>
|
|
|
|
|
Goto Forum:
Current Time: Sun Feb 16 00:35:42 CST 2025
|