CODEWITHSUNDEEP
oracle-databaserow-number
JuneDC
JuneDC

Reputation: 11

Oracle select query that create dummy column with hardcoded value

I have this records, and i want to create an oracle select query to produce dummy column with 1,2,3 as value. Please help thanks.

field1

one
two
three
four
five 
six
seven
eight

basically i want to add dummy column with value from 1 to 3 only. once it reaches 3 it goes back to 1 again.

field1   dummycol
one       1
two       2
three     3
four      1
five      2
six       3
seven     1
eight     2

Upvotes: 1

Views: 6078

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

A simple way is to use the rownum pseudo column and the mod function:

select field1, mod(rownum - 1, 3) + 1
from <your table>;

SQL Fiddle

But that doesn't work quite as expected if the query has an order-by clause, even in a subquery, and presumably you want do want some order to be imposed and the cycling number to be allied against that final order.

You can use the analytic row_number() function to do that instead:

select field1,
  mod(row_number() over (order by null) - 1, 3) + 1 as dummycol
from <your table>
order by field1;

FIELD1     DUMMYCOL
---------- --------
eight             1
five              2
four              3
one               1
seven             2
six               3
three             1
two               2

SQL Fiddle

Upvotes: 1

ErstwhileIII
ErstwhileIII

Reputation: 4843

The mechanism you use here is to include the fixed value as a named column, like

select '1' as one

For your case use something like

select '1' as one, '2' as two, '3' as three, '1' as four, '2' as five, '3' as six, '1' as seven, '3' as eight

This mechanism often comes into play when you are making a union of several queries and want the result to indicate something about each source, like

select name, date, 'student' as Type from student
union
select name, date, 'teacher' as Type from teacher
union
select name, date, 'alumni' as Type from alumni

Upvotes: 1

Related Questions