Home » SQL & PL/SQL » SQL & PL/SQL » Grouping set of data
Grouping set of data [message #252466] Wed, 18 July 2007 23:11 Go to next message
palohotox
Messages: 7
Registered: July 2007
Location: Malaysia
Junior Member

Experts out there..

I need to group set of data based on the commonalities in 4 columns and assign one number for each group..

I've tried order by but my problems is how to check into the point that this 4 columns is not similar and assign the number..

eg..

item,shipdate,shipmode,buyer,vendor,profile

123,2/2/2007,SEA,BG1,ATLAB,ZZZ
345,2/2/2007,SEA,BG1,ISLE,ZZZ
678,2/3/2007,ROAD,BG1,ISLE,ZZZ
891,2/2/2007,AIR,BG1,ISLE,ZZZ
112,2/2/2007,SEA,BG1,ATLAB,ZZZ

The four columns to be used are vendor,shipmode,buyer and profile.. If the value for all these four is the same the they will have single number assigned to them..

Thanks in Advance



Re: Grouping set of data [message #252510 is a reply to message #252466] Thu, 19 July 2007 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on your final goal.
You can get a number for each group with:
select col1, ..., col4, rownum from table group by col1,...,col4;

Regards
Michel
Re: Grouping set of data [message #252517 is a reply to message #252466] Thu, 19 July 2007 01:38 Go to previous messageGo to next message
palohotox
Messages: 7
Registered: July 2007
Location: Malaysia
Junior Member

My Final Goals is to group records with the same values in the four columns and assigned sequential number for each group.. The final number of rows should be the same without aggregation..

it should be like this..

123,2/2/2007,SEA,BG1,ATLAB,ZZZ 001
112,2/2/2007,SEA,BG1,ATLAB,ZZZ 001
891,2/2/2007,AIR,BG1,ISLE ,ZZZ 002
795,2/4/2007,AIR,BG1,ISLE ,ZZZ 002
345,2/2/2007,SEA,BG1,ISLE ,ZZZ 003
678,2/3/2007,ROAD,BG1,ISLE,ZZZ 004

Thanks

[Updated on: Thu, 19 July 2007 01:39]

Report message to a moderator

Re: Grouping set of data [message #252539 is a reply to message #252517] Thu, 19 July 2007 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
My previous query associates a number to each group, just associate a group to each row (which is trivial, doesn't it?).

Regards
Michel
Re: Grouping set of data [message #252574 is a reply to message #252539] Thu, 19 July 2007 03:37 Go to previous messageGo to next message
palohotox
Messages: 7
Registered: July 2007
Location: Malaysia
Junior Member

Hi I've already got it using..

DENSE_RANK () over (ORDER BY col1 || col2|| col3|| col4)




Anyway thanks for the advise
Re: Grouping set of data [message #252590 is a reply to message #252574] Thu, 19 July 2007 05:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post your solution, this may help the next ones that will read this topic.

Regards
Michel
Re: Grouping set of data [message #255835 is a reply to message #252590] Thu, 02 August 2007 00:49 Go to previous message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
may be you can use composite key in group by clause
, i.e grouping sets , or rollups , or even simple grouping function
Previous Topic: ORA-00932: inconsistent datatypes: expected - got CURSER
Next Topic: Display records not having particular code
Goto Forum:
  


Current Time: Thu Dec 08 16:08:23 CST 2016

Total time taken to generate the page: 0.15093 seconds