Home » Other » Training & Certification » Problem in the group by (Oracle 9i)
Problem in the group by [message #282645] Thu, 22 November 2007 20:47 Go to next message
samit_gandhi
Messages: 226
Registered: July 2005
Location: Hong Kong
Senior Member

Hi All

I have some data like this :

Weight Colour
0.46 F
0.46 D
0.46 E
0.46 F
0.46 G
0.47 F
0.49 G
0.50 F
0.56 E
0.58 F
0.59 D

I want the data in grouping like weight between 0.46-0.49,0.50-0.57. in the order of the colour

How to do it pls let me know

Thx in advance.

Samit gandhi




Re: Problem in the group by [message #282647 is a reply to message #282645] Thu, 22 November 2007 20:57 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
How did you decide to break the range at 0.49/0.50?

Is it based on a change of the 1st digit after the decimal point?

Show us some sample output as well.

Ross Leishman
Re: Problem in the group by [message #282648 is a reply to message #282647] Thu, 22 November 2007 20:59 Go to previous messageGo to next message
samit_gandhi
Messages: 226
Registered: July 2005
Location: Hong Kong
Senior Member

0.46 F
0.47 F
0.48 F
0.46 G
0.46 G
0.47 G
0.51 E
0.52 E
0.52 F

THX


Re: Problem in the group by [message #282652 is a reply to message #282645] Thu, 22 November 2007 22:08 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Hi Samit,
Send create table scripts and insert scripts

Regards,
Kiran
Re: Problem in the group by [message #282654 is a reply to message #282652] Thu, 22 November 2007 22:15 Go to previous messageGo to next message
samit_gandhi
Messages: 226
Registered: July 2005
Location: Hong Kong
Senior Member

Dear Kiran,

I have problem with the sql query no table structure is thre.

Need the ordering of the record as per the previous foram. In that there are two fields like weight and colour.

samit gandhi

Re: Problem in the group by [message #282667 is a reply to message #282645] Thu, 22 November 2007 23:25 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Are u trying something like ..

CREATE TABLE OF_NUM
(
  OF_NUMBER  NUMBER(5,2)
);

Insert into OF_NUM
   (OF_NUMBER)
 Values
   (0.46);
Insert into OF_NUM
   (OF_NUMBER)
 Values
   (0.5);
Insert into OF_NUM
   (OF_NUMBER)
 Values
   (0.56);
Insert into OF_NUM
   (OF_NUMBER)
 Values
   (0.59);
Insert into OF_NUM
   (OF_NUMBER)
 Values
   (0.46);
Insert into OF_NUM
   (OF_NUMBER)
 Values
   (0.46);
Insert into OF_NUM
   (OF_NUMBER)
 Values
   (0.47);
Insert into OF_NUM
   (OF_NUMBER)
 Values
   (0.49);
Insert into OF_NUM
   (OF_NUMBER)
 Values
   (0.46);
Insert into OF_NUM
   (OF_NUMBER)
 Values
   (0.46);
Insert into OF_NUM
   (OF_NUMBER)
 Values
   (0.58);
COMMIT;

select distinct --OF_NUMBER , trunc(OF_NUMBER,1) ,
	   dense_rank() over (Order by  trunc(OF_NUMBER,1)    ) rnk ,
	   mIN(OF_NUMBER) over (partition by  trunc(OF_NUMBER,1)  ) MN,
	    max(OF_NUMBER) over (partition by  trunc(OF_NUMBER,1)  ) MX
from OF_num


Thumbs Up
Rajuvan.
Re: Problem in the group by [message #282689 is a reply to message #282645] Fri, 23 November 2007 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

If it is a homework, post it "homework" forum.

Regards
Michel
Re: Problem in the group by [message #282698 is a reply to message #282648] Fri, 23 November 2007 01:17 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from t order by weight, colour;
    WEIGHT COLOUR
---------- ------
       .46 D
       .46 E
       .46 F
       .46 F
       .46 G
       .47 F
       .49 G
        .5 F
       .56 E
       .58 F
       .59 D

11 rows selected.

SQL> select trunc(10*weight) grp, weight, colour 
  2  from t
  3  order by grp, colour, weight
  4  /
       GRP     WEIGHT COLOUR
---------- ---------- ------
         4        .46 D
         4        .46 E
         4        .46 F
         4        .46 F
         4        .47 F
         4        .46 G
         4        .49 G
         5        .59 D
         5        .56 E
         5         .5 F
         5        .58 F

11 rows selected.

Regards
Michel

Previous Topic: Index
Next Topic: Query to implement search functionality
Goto Forum:
  


Current Time: Fri Mar 29 09:24:18 CDT 2024