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 Go to next message
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 #340536 is a reply to message #340535] Wed, 13 August 2008 04:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case: create table and insert statements along with the result you want with these data.

Also, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Need help on sorting per column 1, then min/max from column 2 and corresponding column 3 [message #340664 is a reply to message #340535] Wed, 13 August 2008 13:38 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
hint... use group by
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 Go to previous messageGo to next message
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 #340716 is a reply to message #340705] Wed, 13 August 2008 23:37 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
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> 

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 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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>
Re: Please explain KEEP & DENSE_RANK [message #340731 is a reply to message #340730] Thu, 14 August 2008 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't start a new topic, continue on the previous one.

Did you read the documentation? FIRST

And read OraFAQ Forum Guide, especially "How to format your post?" section.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Thu, 14 August 2008 01:17]

Report message to a moderator

Re: Please explain KEEP & DENSE_RANK [message #340739 is a reply to message #340731] Thu, 14 August 2008 01:52 Go to previous message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Have a look at this:-

http://www.oracle-base.com/articles/misc/RankDenseRankFirstLastAnalyticFunctions.php

Regards,
Rajat
Previous Topic: Issues with MV refresh
Next Topic: What's wrong with this PROC?
Goto Forum:
  


Current Time: Sun Dec 11 08:25:11 CST 2016

Total time taken to generate the page: 0.10071 seconds