Home » Other » Training & Certification » Problem in the group by (Oracle 9i)
Problem in the group by Thu, 22 November 2007 20:47
 samit_gandhi Messages: 226Registered: 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

Samit gandhi

Re: Problem in the group by [message #282647 is a reply to message #282645] Thu, 22 November 2007 20:57
 rleishman Messages: 3727Registered: 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
 samit_gandhi Messages: 226Registered: 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
 kir_ait Messages: 198Registered: 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
 samit_gandhi Messages: 226Registered: 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
 rajavu1 Messages: 1574Registered: 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```

Rajuvan.
Re: Problem in the group by [message #282689 is a reply to message #282645] Fri, 23 November 2007 00:42
 Michel Cadot Messages: 65153Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
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
 Michel Cadot Messages: 65153Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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: Request For a Query Next Topic: SQL injection
Goto Forum:

Current Time: Tue Aug 22 17:45:54 CDT 2017

Total time taken to generate the page: 0.04981 seconds