| Grouping of records in a table with a group id [message #668077] |
Sat, 03 February 2018 16:40  |
bahubcd
Messages: 40
Registered: July 2007
Location: Bangalore
|
Member |
|
|
Hi all,
I have a requirement to update a group of records in a table with a a number generated by a sequence. Below is the data in the table items. There are only 2 columns Item,grp_id.
select item from items;
Item
-------------
123
234
458
563
983
989
837
289
239
239
222
The grp_id column should be updated with the value from the sequence for every 3 records. The data should look like below.
select item,grp_id from items;
Item Grp_ID
-------------
123 1
234 1
458 1
563 2
983 2
989 2
837 3
289 3
239 3
239 4
222 4
Can we get the second column Grp_ID with a single update or merge statement?
Can anyone help?
Thanks
Bahubcd
[EDITED by LF: fixed [code] tags]
[Updated on: Sun, 04 February 2018 05:39] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Grouping of records in a table with a group id [message #668080 is a reply to message #668078] |
Sun, 04 February 2018 06:03  |
 |
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
As you didn't explain which triples make a set, I sorted them first (the SRT) and then checked whether item's ordinal number can be divided by 3, and that makes the final result (RK).
SQL> with items (item) as
2 (select 123 from dual union all
3 select 234 from dual union all
4 select 458 from dual union all
5 select 563 from dual union all
6 select 983 from dual union all
7 select 989 from dual union all
8 select 837 from dual union all
9 select 289 from dual union all
10 select 239 from dual union all
11 select 239 from dual union all
12 select 222 from dual
13 ),
14 srt as
15 (select item, row_number() over (order by item) rn
16 from items
17 )
18 select item,
19 row_number() over (partition by mod(rn, 3) order by rn) rk
20 from srt
21 order by rn;
ITEM RK
---------- ----------
123 1
222 1
234 1
239 2
239 2
289 2
458 3
563 3
837 3
983 4
989 4
11 rows selected.
SQL>
Someone else might (and probably will) have another suggestion & solution.
[Updated on: Sun, 04 February 2018 06:04] Report message to a moderator |
|
|
|
| Re: Grouping of records in a table with a group id [message #668083 is a reply to message #668080] |
Sun, 04 February 2018 08:33 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
If the order is random, then this works:orclx> with items (item) as
2 (select 123 from dual union all
3 select 234 from dual union all
4 select 458 from dual union all
5 select 563 from dual union all
6 select 983 from dual union all
7 select 989 from dual union all
8 select 837 from dual union all
9 select 289 from dual union all
10 select 239 from dual union all
11 select 239 from dual union all
12 select 222 from dual
13 )
14 select item,ceil(rownum/3) from items;
ITEM CEIL(ROWNUM/3)
---------- --------------
123 1
234 1
458 1
563 2
983 2
989 2
837 3
289 3
239 3
239 4
222 4
11 rows selected.
orclx> |
|
|
|
|
|
|
|
| Re: Grouping of records in a table with a group id [message #668290 is a reply to message #668077] |
Fri, 16 February 2018 07:44 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
OP, unless you define an order for the rows, there is no way to do what you want. Order of insert into the table is not a valid order in a relational database.
I like Littlefoot's puzzle solving attempt. John's solution seems to match your output but be rest assured, it was just plain luck as he clearly mentioned it is random.
|
|
|
|
|
|
|
|
| Re: Grouping of records in a table with a group id [message #668352 is a reply to message #668302] |
Mon, 19 February 2018 05:30 |
 |
browncat
Messages: 9
Registered: May 2015
|
Junior Member |
|
|
After the command,
ALTER TABLE items ADD grp_id int;
in case there are no rows appended then insert can be used.......
INSERT INTO ITEMS (item, grp_id) VALUES (123, 1);
INSERT INTO ITEMS (item, grp_id) VALUES (456, 1);
INSERT INTO ITEMS (item, grp_id) VALUES (789, 1);
INSERT INTO ITEMS (item, grp_id) VALUES (563, 2);
etc.....
|
|
|
|
| Re: Grouping of records in a table with a group id [message #668358 is a reply to message #668352] |
Mon, 19 February 2018 06:33 |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
browncat wrote on Mon, 19 February 2018 05:30After the command,
ALTER TABLE items ADD grp_id int;
in case there are no rows appended then insert can be used.......
INSERT INTO ITEMS (item, grp_id) VALUES (123, 1);
INSERT INTO ITEMS (item, grp_id) VALUES (456, 1);
INSERT INTO ITEMS (item, grp_id) VALUES (789, 1);
INSERT INTO ITEMS (item, grp_id) VALUES (563, 2);
etc.....
And how is this really any different than your previous post in this thread?
Why are all of your posts in this forum similar to this - overyly simplistic to the point of being pointless.
Why do you never respond when you are called out? Are you, like the OP, a WORN (Write Once Read Never) forum participant?
|
|
|
|
| Re: Grouping of records in a table with a group id [message #668399 is a reply to message #668280] |
Wed, 21 February 2018 06:51 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
browncat wrote on Fri, 16 February 2018 07:07Use the query...
ALTER TABLE items ADD grp_id int;
UPDATE items SET grp_id = 1 WHERE item = 123;
So on update each record in the table with the UPDATE query...
This is the worst way you can do it. Use one of the suggestions above
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
