Home » SQL & PL/SQL » SQL & PL/SQL » shuffling of data (oracle 10.2.0.1)
shuffling of data [message #399166] Tue, 21 April 2009 00:15 Go to next message
gaikwadrachit
Messages: 33
Registered: June 2007
Location: mumbai
Member
Hi

Can any one tell me how to shuffle data in a column.
data in a column can be varchar or number.

eg

Emp_no emp_name
123 robert
342 jack
6456 tom
3232 peter
454 caprio


I want an output like
Emp_no emp_name
123 borelr
342 jcak
6456 tmo
3232 teerp
454 paorio
Re: shuffling of data [message #399167 is a reply to message #399166] Tue, 21 April 2009 00:18 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: shuffling of data [message #399170 is a reply to message #399166] Tue, 21 April 2009 00:28 Go to previous messageGo to next message
gaikwadrachit
Messages: 33
Registered: June 2007
Location: mumbai
Member
No Message Body
  • Attachment: dml.sql
    (Size: 0.33KB, Downloaded 189 times)
Re: shuffling of data [message #399179 is a reply to message #399170] Tue, 21 April 2009 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Some of us can't or don't want to download, but a short but representative test case INLINE and post it formatted as per OraFAQ Forum Guide.

In addition, explain your output. What is the rule?

Regards
Michel
Re: shuffling of data [message #399181 is a reply to message #399166] Tue, 21 April 2009 00:46 Go to previous messageGo to next message
gaikwadrachit
Messages: 33
Registered: June 2007
Location: mumbai
Member
No Message Body
  • Attachment: ddl.sql
    (Size: 0.31KB, Downloaded 170 times)
Re: shuffling of data [message #399183 is a reply to message #399166] Tue, 21 April 2009 00:47 Go to previous messageGo to next message
gaikwadrachit
Messages: 33
Registered: June 2007
Location: mumbai
Member
it should shuffle randomly
Re: shuffling of data [message #399184 is a reply to message #399183] Tue, 21 April 2009 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still not able to see the test case.

Regards
Michel
Re: shuffling of data [message #399185 is a reply to message #399166] Tue, 21 April 2009 00:48 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
Post DDL for tables.
Post DML for test data.

Post expected/desired results.


Re: shuffling of data [message #399186 is a reply to message #399183] Tue, 21 April 2009 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
gaikwadrachit wrote on Tue, 21 April 2009 07:47
it should shuffle randomly

dbms_random

Regards
Michel
Re: shuffling of data [message #399234 is a reply to message #399166] Tue, 21 April 2009 03:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to select each of the columns that you want to sort independently, along with a densely populated order column that lists the position in the list that each value should come at.

You then join the seperate queries on the Order column:
with src as (select 123  empno, 'robert' ename from dual union all
             select 342  ,'jack'   from dual union all
             select 6456 ,'tom'    from dual union all
             select 3232 ,'peter'  from dual union all
             select  454  ,'caprio' from dual)
select empno,ename
from   (select empno,row_number() over (order by empno) empno_ord
        from   src) s1
      ,(select ename,row_number() over (order by ename) ename_ord
        from   src) s2
where  s1.empno_ord = s2.ename_ord;
Re: shuffling of data [message #399236 is a reply to message #399234] Tue, 21 April 2009 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you didn't notice (I didn't at first read) that name letters are scrambled.

Regards
Michel
Re: shuffling of data [message #399237 is a reply to message #399236] Tue, 21 April 2009 04:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You're quite right - I missed that completely.

Re: shuffling of data [message #399292 is a reply to message #399166] Tue, 21 April 2009 08:10 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
gaikwadrachit wrote on Tue, 21 April 2009 01:15

Emp_no emp_name
123 robert
454 caprio


I want an output like
Emp_no emp_name
123 borelr
454 paorio



Explain how the two rows for the input are related the the output.
Previous Topic: stored procedure with cursors
Next Topic: Distinct with NULL
Goto Forum:
  


Current Time: Mon Dec 05 06:54:26 CST 2016

Total time taken to generate the page: 0.14477 seconds