| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: A difficult request
Joe,
Strange request from your boss. But here is one way to achieve it.
Given table foo...
SQL> desc foo
Name Null? Type ----------------------------------------- -------- ------------------------ ---- ID1 NUMBER ID2 NUMBER
SQL> select * from foo
2 /
ID1 ID2
---------- ----------
1 1
2 2
3 3
4 4
The following will randomly get only one row...
SQL> l
1 select f.id1, f.id2
2 from ( select count(*) cnt from foo ) c,
3 ( select foo.*, rownum r from foo ) f
4* where trunc(dbms_random.value(1,c.cnt+1)) = f.r
SQL> /
ID1 ID2
---------- ----------
3 3
SQL> /
ID1 ID2
---------- ----------
3 3
SQL> /
ID1 ID2
---------- ----------
1 1
SQL> /
ID1 ID2
---------- ----------
2 2
Make this query you view.
hope this helps.
chris.
-- Christopher Beck, Principal Technologist, Oracle Corporation, christopher.beck_at_oracle.com Beginning Oracle Programming, http://www.amazon.com/exec/obidos/ASIN/186100690X "Joe Bayer" <u705413818_at_spawnkill.ip-mobilphone.net> wrote in message news:l.1021582008.1213226318@[64.94.198.252]...Received on Fri May 17 2002 - 07:41:58 CDT
> My boss asks me to let user select from a table for only one row each
> time, which is very difficult to achieve
>
> for example
>
> SQL> select * from test;
>
> SCHEMA_NAME SCHEMA_VALUE
> -------------------- --------------------
> A a
> B b
> C c
> D d
> E e
> F f
>
> 6 rows selected.
>
> Each time, we just want user to see one row,
> so I created a view
>
> 1 CREATE OR REPLACE VIEW test_view AS
> 3 SELECT *
> 4 FROM test
> 5* WHERE rownum < 2
> SQL> /
>
> View created.
>
> but I can only select the first row of the table
>
> 1 select * from test_view
> 2* where schema_name='A'
> SQL> /
>
> SCHEMA_NAME SCHEMA_VALUE
> -------------------- --------------------
> A a
>
> 1 select * from test_view
> 2* where schema_name='B'
> SQL> /
>
> no rows selected
>
> can somebody help?
>
> thanks
>
>
>
>
>
>
>
> --
> Sent by joebayerii from hotmail subpart from com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com/cgi/content/new
 |
 |