Appending Row Data to Select Statement in Oracle DB

Question

So I am getting the values at specified columns at random rows within a table such that:

SELECT <COLUMN_NAME> 
  FROM ( SELECT <COLUMN_NAME> 
           FROM <SCHEMA>.<TABLE_NAME>
          ORDER BY dbms_random.value ) 
 WHERE rownum < <RANDOM_NUMBER>

And that works as it should. Now I want to append the data for the entire row to the result rows. For example if my table is:

Column A            Column B             Column C         Column D
data1                data2                data3            data4
data5                data6                data7            data8

If my specified column is column B, my query returns:

data2

data6

I want it to return:

data2, data1, data2, data3, data4
data6, data5, data6, data7, data8

I'm assuming its something like SELECT < COLUMN_NAME >, * ........?

Matthew McPeak · Accepted Answer · 2015-08-14 20:01:34Z

1

This should work, if < COLUMN_NAME > is unique in the table:

SELECT r.<COLUMN_NAME>, t_append.* FROM (
SELECT <COLUMN_NAME> FROM <TABLE_NAME> t_random ORDER BY dbms_random.random ) r
inner join <TABLE_NAME> t_append on t_append.<COLUMN_NAME> = r.<COLUMN_NAME>
WHERE rownum <= dbms_random.value(1,<MAX_RANDOM_NUMBER_YOU_WANT>)

Update based on additional requirements (multiple columns)

SELECT r.<COLUMN_NAME_1>, r.<COLUMN_NAME_2>, etc, t_append.* 
FROM (
    SELECT <COLUMN_NAME_1>, r.<COLUMN_NAME_2>, etc..
    FROM <TABLE_NAME> t_random ORDER BY dbms_random.random ) r
    inner join <TABLE_NAME> t_append on 
        t_append.<COLUMN_NAME_1> = r.<COLUMN_NAME_1> 
    AND t_append.<COLUMN_NAME_2> = r.<COLUMN_NAME_2> 
    AND etc...
    WHERE rownum <= dbms_random.value(1,<MAX_RANDOM_NUMBER_YOU_WANT>)

Again... it only works if the set of columns you use uniquely identifies a record in your table.

edited Aug 14, 2015 at 20:01

answered Aug 14, 2015 at 19:36

Matthew McPeak

18.1k3 gold badges32 silver badges64 bronze badges

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

14 Comments

user2665166 Over a year ago

I should have added: I would like to be able to specify multiple columns as well. So if I specified column B and C, in the result I would get B's value and C's value followed by the entire row. How would I restructure the SQL to accept that?

user2665166 Over a year ago

You rock Matthew, thanks a lot. And I know the columns need to be unique. I'm actually using this statement with my composite keys.

user2665166 Over a year ago

Hey, how would I correctly modify this statement if instead of finding random rows. I had specific values in column 1 and column 2 which I wanted to find the row for?

Matthew McPeak Over a year ago

In the inner select, replace "WHERE rownum<= ..." with "WHERE <column_name> = <value you want in column> AND <other_column_name> = <value you want in other column>"

Matthew McPeak Over a year ago

Use (columnA, columnB) IN ( ('1st col A value','1st col B value'), ('2nd col A value','2nd col B value'), ... etc)

|

Collectives™ on Stack Overflow

Appending Row Data to Select Statement in Oracle DB

1 Answer 1

Update based on additional requirements (multiple columns)

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Update based on additional requirements (multiple columns)

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related