Home » SQL & PL/SQL » SQL & PL/SQL » show result random
show result random [message #448549] Wed, 24 March 2010 01:15 Go to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear all,
I run follwing query.
select * from crp_00_08

it shows result like
BAS_COD	BAS_DES

1	MAIN GATE (FRONT)
2	NEAR MILL # 3, 3-1
3	NEAR MILL # 3, 3-2
4	NEAR MILL # 3, 3-3
5	NEAR MILL # 3, 3-4
10	NEAR MILL # 5, 5-1
11	NEAR MILL # 5, 5-2
12	NEAR MILL # 5, 5-3
13	NEAR MILL # 5, 5-4
14	NEAR MILL # 5, 5-5
15	NEAR MILL # 5, 5-6
16	NEAR MILL # 5, 5-7
17	NEAR MILL # 5, 5-8
18	NEAR MILL # 5, 5-9
19	NEAR MILL # 5, 5-10
20	NEAR MILL # 5, 5-11


i want that when i run above query every time bas_cod column should start with difference value.
now it is starting from 1, i need when i run next time it should start with another number except 1,

i mean whenever i run above query,order of bas_cod must be change from last.

how can i do it.

Thanks in advance.
Asif
Re: show result random [message #448552 is a reply to message #448549] Wed, 24 March 2010 01:19 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>i mean whenever i run above query,order of bas_cod must be change from last.

SQL is dumb & knows nothing about "history"
so how do YOU propose to make it smart & "remember"?
Re: show result random [message #448555 is a reply to message #448549] Wed, 24 March 2010 01:25 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

ok. but how can i solve problem, because when ever i run query it is sorted by same order every time.
Re: show result random [message #448558 is a reply to message #448555] Wed, 24 March 2010 01:35 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
mamalik wrote on Wed, 24 March 2010 01:25
ok. but how can i solve problem, because when ever i run query it is sorted by same order every time.


software do should that
Re: show result random [message #448561 is a reply to message #448549] Wed, 24 March 2010 01:38 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

i need it in query, any tip will be appreciated.
Re: show result random [message #448565 is a reply to message #448561] Wed, 24 March 2010 01:48 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Read about dbms_random.value/ SAMPLE
Quote:

SQL> SELECT * FROM (SELECT ename
2 FROM emp
3 ORDER BY dbms_random.value())
4 WHERE rownum <= 3;

Thanks
Ved

[Updated on: Wed, 24 March 2010 01:49]

Report message to a moderator

Re: show result random [message #448567 is a reply to message #448561] Wed, 24 March 2010 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
now it is starting from 1, i need when i run next time it should start with another number except 1,

Create a sequence and then select where bas_cod=seq.nextval


Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand

[Updated on: Wed, 24 March 2010 01:51]

Report message to a moderator

Re: show result random [message #448568 is a reply to message #448549] Wed, 24 March 2010 01:54 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

now it is starting from 1, i need when i run next time it should start with another number except 1,

i mean whenever i run above query,order of bas_cod must be change from last.

Please provide test case(create table and Insert statement) and your expected output.
Re: show result random [message #448574 is a reply to message #448549] Wed, 24 March 2010 02:17 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear Michle Uncle

Quote:
Create a sequence and then select where bas_cod=seq.nextval


if i generate a sequence and user Quote:
where bas_cod=seq.nextval
then bas_cod will not be shown in next query against sequence number which i have already used. but we can do it by using outer join.

another problem is that user can find out that which value is comming next by using sequence.

Regards
Asif.



Re: show result random [message #448575 is a reply to message #448574] Wed, 24 March 2010 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
then bas_cod will not be shown in next query against sequence number which i have already used

It was an idea to explore not a solution.
Here's another idea:
order by decode(cod_bas,seq.nextval,0,cod_bas)

Quote:
another problem is that user can find out that which value is comming next by using sequence.

So select the sequence value too.

Regards
Michel

[Updated on: Wed, 24 March 2010 02:20]

Report message to a moderator

Re: show result random [message #448578 is a reply to message #448574] Wed, 24 March 2010 02:28 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
What speaks against the method from @its_me_ved with dbms_random() ?
SET SERVEROUTPUT ON SIZE 100000;

DECLARE
  act  INTEGER        := 0;
  n    VARCHAR2 (255);
BEGIN
  FOR i IN 1 .. 10 LOOP
    WITH DATA AS
         (SELECT 1 ID, 'GATE' nam FROM DUAL UNION ALL
          SELECT 2,    'MILL'     FROM DUAL UNION ALL
          SELECT 3,    'HOUSE'    FROM DUAL UNION ALL
          SELECT 4,    'TOWER'    FROM DUAL)
    SELECT  ID, nam
      INTO act,   n
      FROM (SELECT ID, nam FROM DATA WHERE ID <> act ORDER BY DBMS_RANDOM.VALUE)
     WHERE ROWNUM = 1;

    DBMS_OUTPUT.put_line ('ID='|| act || ' nam=' || n);
  END LOOP;
END;

ID=3 nam=HOUSE
ID=1 nam=GATE
ID=3 nam=HOUSE
ID=4 nam=TOWER
ID=1 nam=GATE
ID=3 nam=HOUSE
ID=1 nam=GATE
ID=4 nam=TOWER
ID=2 nam=MILL
ID=1 nam=GATE

Re: show result random [message #448592 is a reply to message #448578] Wed, 24 March 2010 03:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think (if I understood) the issue is to get all rows order by cod_bas but with the first row different each time (which is at most about each time as there is not an unlimited number of cod_bas).

Regards
Michel

[Updated on: Wed, 24 March 2010 03:20]

Report message to a moderator

Re: show result random [message #448594 is a reply to message #448592] Wed, 24 March 2010 03:27 Go to previous message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
The Title and the requiremnt the OP is looking for is quite confusing.
Its difficult to understand when he says:

Quote:

i mean whenever i run above query,order of bas_cod must be change from last.


It would have been good if the OP post a test case (with the requirement result)

[Updated on: Wed, 24 March 2010 03:30]

Report message to a moderator

Previous Topic: Trigger to move records to history
Next Topic: Help in sql Query .
Goto Forum:
  


Current Time: Sat Dec 03 16:05:29 CST 2016

Total time taken to generate the page: 0.20911 seconds