Home » SQL & PL/SQL » SQL & PL/SQL » Grouping of records in a table with a group id (11G, Unix)
Grouping of records in a table with a group id [message #668077] Sat, 03 February 2018 16:40 Go to next message
bahubcd
Messages: 39
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
I have created a new sequence grp_seq with starting value = 1 and increment by 1.

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 #668078 is a reply to message #668077] Sun, 04 February 2018 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 65970
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select item,grp_id from items;
select item,grp_id from items
                        *
ERROR at line 1:
ORA-00942: table or view does not exist
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 Go to previous messageGo to next message
Littlefoot
Messages: 21446
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 Go to previous messageGo to next message
John Watson
Messages: 7669
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 #668280 is a reply to message #668083] Fri, 16 February 2018 06:07 Go to previous messageGo to next message
browncat
Messages: 9
Registered: May 2015
Junior Member
Use 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...
Re: Grouping of records in a table with a group id [message #668282 is a reply to message #668280] Fri, 16 February 2018 06:21 Go to previous messageGo to next message
EdStevens
Messages: 1005
Registered: September 2013
Senior Member
browncat wrote on Fri, 16 February 2018 06:07
Use 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...
So you are suggesting to OP write a hard-coded update statement for each row in the table. How practical is that?
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 Go to previous messageGo to next message
joy_division
Messages: 4909
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 #668301 is a reply to message #668290] Sat, 17 February 2018 07:38 Go to previous messageGo to next message
EdStevens
Messages: 1005
Registered: September 2013
Senior Member
It appears the OP subscribes to the practice of WORN (Write Once Read Never). It's been two weeks since he started this thread and has not responded to a single suggestion or question.
Re: Grouping of records in a table with a group id [message #668302 is a reply to message #668301] Sat, 17 February 2018 08:04 Go to previous messageGo to next message
Michel Cadot
Messages: 65970
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And has not responded to any of his other topics.

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
EdStevens
Messages: 1005
Registered: September 2013
Senior Member
browncat wrote on Mon, 19 February 2018 05:30
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.....
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 Go to previous message
Bill B
Messages: 1814
Registered: December 2004
Senior Member
browncat wrote on Fri, 16 February 2018 07:07
Use 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
Previous Topic: NVL Vs OR condition
Next Topic: SQL Query
Goto Forum:
  


Current Time: Sat Nov 17 15:31:30 CST 2018