SQL (PLSQL) number a set of rows

Question

I attended an interview recently and the interviewer asked me number the occurrences of 'A', 'B', 'C' and so on. To put in table and columns - there is a table tab with column as col. The values in col is 'A', 'B', 'C' etc.

create table tab226 (col varchar2(3) );

insert into tab226 VALUES ('A');
insert into tab226 VALUES ('B');
insert into tab226 VALUES ('C');

insert into tab226 VALUES ('B');
insert into tab226 VALUES ('A');
insert into tab226 VALUES ('C');

insert into tab226 VALUES ('C');
insert into tab226 VALUES ('A');
insert into tab226 VALUES ('B');

The expected output is :

Interviewer told me I can use SQL or PLSQL to achieve it. I thought about it for almost 10 mins but couldn't come up with a plan let alone the solution. Does anyone know if this can be achieved in Oracle SQL or PLSQL?

scratch the post - I definitely forgot some other points in that question, I guess I remember only few points. But the above can be achieved by row_number() (partition by order by) — Arty155
– Arty155, Commented Jun 21, 2021 at 20:56
Wow! Thats fast! I did not expect such fast response. I will definitely try to remember the complete question. — Arty155
– Arty155, Commented Jun 21, 2021 at 20:58

Littlefoot · Accepted Answer · 2021-06-21 20:49:44Z

1

Doesn't make much sense to me, but - would this do?

SQL> select col,
  2    count(*) over (partition by col order by rowid) exp_output
  3  from tab226
  4  order by rowid;

COL EXP_OUTPUT
--- ----------
A            1
B            1
C            1
B            2
A            2
C            2
C            3
A            3
B            3

9 rows selected.

SQL>

answered Jun 21, 2021 at 20:49

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Arty155 Over a year ago

Yes, it works. Thanks for being so fast :)

Gordon Linoff · Accepted Answer · 2021-06-21 20:49:30Z

1

As written, you cannot accomplish -- consistently -- what they are asking for. The problem is that SQL tables represent unordered sets. There is no way to run a query on the original data and preserve the ordering.

However, the final column appears to simply be an enumeration, so you can use row_number() for that:

select col, row_number() over (partition by col order by NULL)
from tab226;

But if you have an ordering column -- say id in this example -- then you would do:

select col, row_number() over (partition by col order by NULL)
from tab226;

Here is a db<>fiddle.

answered Jun 21, 2021 at 20:49

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL (PLSQL) number a set of rows

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related