Re: SQL question

"Jason D." wrote:

> I have a table called EMPLOYEES and I have a select statement with which I
> retrieve records that fit certain criteria.
>
> Is there a way to tweak the select to return only ONE of these records and
> preferably a random one?
>
> I want to use this select in an automated test case and want to pass only
> one set of values at a time.

WHERE rownum = 1

And while it will be the first record ... it will also be random as the record that is the first record, from a heap table, is not necessarily and different from any other record.

If you truly want random then you will probably want to use an in-line view and do something like this:

i := randomly generated number (look at the DBMS_RANDOM built-in package)

SELECT *
FROM (
   SELECT rownum RN_ALIAS, field1, field2, field3    FROM table
   WHERE rownum <= i)
WHERE RN_ALIAS = i;

Daniel Morgan Received on Mon Feb 10 2003 - 11:13:52 CST