Updating a table with group by [message #632094] |
Sat, 24 January 2015 21:25 |
wantmannu
Messages: 42 Registered: June 2009 Location: cali
|
Member |
|
|
I have a table with three columns (Product Number, Product Type, Batch Number). It has 100 rows of data in Product number and type.
I would like update the batch number for every 10 records max of same product type. (There are only 3 product types)
Can you please help how can I write an update query satisfying this need.
[Updated on: Sat, 24 January 2015 21:26] Report message to a moderator
|
|
|
|
|
|
|
Re: Updating a table with group by [message #632105 is a reply to message #632103] |
Sun, 25 January 2015 01:29 |
|
Michel Cadot
Messages: 68619 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
With any SQL or PL/SQL question, please, Post a working [[Test case]]: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
In addition to help to help you and so have more people trying to solve your problem, you will see that often you can write the query (or see how to write it) and realize if they are complete or not, consistent or not.
A such test case for your current problem could be something like:
create table t (id integer, t varchar2(1));
insert into t values (1, 'A');
insert into t values (2, 'A');
insert into t values (3, 'B');
insert into t values (4, 'B');
insert into t values (5, 'C');
insert into t values (6, 'B');
insert into t values (7, 'A');
insert into t values (8, 'C');
insert into t values (9, 'B');
insert into t values (10, 'B');
insert into t values (11, 'C');
insert into t values (12, 'C');
insert into t values (13, 'A');
insert into t values (14, 'B');
insert into t values (15, 'B');
insert into t values (16, 'C');
insert into t values (17, 'A');
commit;
A starter to solve it may be:
SQL> select id, t,
2 4 * (ascii(t)-65)
3 + trunc((row_number() over (partition by t order by id)-1)/2)
4 batch
5 from t
6 order by id
7 /
ID T BATCH
---------- - ----------
1 A 0
2 A 0
3 B 4
4 B 4
5 C 8
6 B 5
7 A 1
8 C 8
9 B 5
10 B 6
11 C 9
12 C 9
13 A 1
14 B 6
15 B 7
16 C 10
17 A 2
[Updated on: Sun, 25 January 2015 01:30] Report message to a moderator
|
|
|
|