Home » SQL & PL/SQL » SQL & PL/SQL » select single record not using where clause
select single record not using where clause [message #244563] Wed, 13 June 2007 06:25 Go to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Hi All,
Is there any clause in oracle by which I can select 1 row of a table like
SELECT SINGLE ... 
or
SELECT UP TO 1 ROWS ...
in SAP.

or is there other way to select it not using where clause?

Regards
Sanka
Re: select single record not using where clause [message #244569 is a reply to message #244563] Wed, 13 June 2007 06:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SELECT ... WHERE ROWNUM=1

Regards
Michel
Re: select single record not using where clause [message #244570 is a reply to message #244569] Wed, 13 June 2007 06:38 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Michel,
I do not want to use the where clause.
Re: select single record not using where clause [message #244575 is a reply to message #244570] Wed, 13 June 2007 06:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is the way to do it in Oracle.
If you don't want to use this clause, don't use Oracle.

Regards
Michel

[Updated on: Wed, 13 June 2007 06:51]

Report message to a moderator

Re: select single record not using where clause [message #244576 is a reply to message #244570] Wed, 13 June 2007 06:51 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
How would you know what record you'll retrieve? Just any random row would do?

MHE
Re: select single record not using where clause [message #244578 is a reply to message #244576] Wed, 13 June 2007 06:58 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
DISTINCT + analytics = ugly but it's not a WHERE clause:
SQL> DESC jobs
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------

 JOB_ID                                    NOT NULL VARCHAR2(10)
 JOB_TITLE                                 NOT NULL VARCHAR2(35)
 MIN_SALARY                                         NUMBER(6)
 MAX_SALARY                                         NUMBER(6)

SQL>
SQL>
SQL> SELECT DISTINCT
  2         FIRST_VALUE(job_id    ) OVER ( ORDER BY job_id) job_id
  3       , FIRST_VALUE(job_title ) OVER ( ORDER BY job_id) job_title
  4       , FIRST_VALUE(min_salary) OVER ( ORDER BY job_id) min_salary
  5       , FIRST_VALUE(max_salary) OVER ( ORDER BY job_id) max_salary
  6  FROM   jobs
  7  /

JOB_ID     JOB_TITLE                           MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AC_ACCOUNT Public Accountant                         4200       9000


MHE
Re: select single record not using where clause [message #244579 is a reply to message #244578] Wed, 13 June 2007 07:03 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

Thanks Maaher.
I am just looking for it.
Thanks again.

Regards
Sanka
Re: select single record not using where clause [message #244602 is a reply to message #244579] Wed, 13 June 2007 08:43 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Here's another possibility. If you know the number of records in the table you can calculate the sample size:

SQL> SELECT 100/num_rows sample_size
  2  FROM   user_tables
  3  WHERE  table_name = 'EMPLOYEES';

SAMPLE_SIZE
-----------
 .934579439

SQL>
SQL> SELECT employee_id
  2  FROM   employees SAMPLE(0.934579439)
  3  /

EMPLOYEE_ID
-----------
        201


MHE
Re: select single record not using where clause [message #244604 is a reply to message #244578] Wed, 13 June 2007 08:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please, don't encourage people to do stupid things or add a warning (like "this is stupid but you can do it like this").

Regards
Michel

[Updated on: Wed, 13 June 2007 08:51]

Report message to a moderator

Re: select single record not using where clause [message #244606 is a reply to message #244604] Wed, 13 June 2007 08:53 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
All work, no play Sad

MHE

(just kidding, Michel Wink)
Re: select single record not using where clause [message #244784 is a reply to message #244563] Thu, 14 June 2007 02:13 Go to previous messageGo to next message
swathimanorama
Messages: 2
Registered: June 2007
Junior Member
hi,
we can use 'sample' clause to select single row with out specifying where condition.

select * from emp sample(10);
Re: select single record not using where clause [message #244785 is a reply to message #244784] Thu, 14 June 2007 02:14 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
swathimanorama wrote on Thu, 14 June 2007 09:13
hi,
we can use 'sample' clause to select single row with out specifying where condition.
That's what I said, yes. Thanks for confirming but you're a bit late Wink

MHE
Re: select single record not using where clause [message #244796 is a reply to message #244785] Thu, 14 June 2007 02:36 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
  1  select table_name
  2  from dba_tables
  3  group by table_name, rownum
  4* having rownum = 1
SQL> /

TABLE_NAME
------------------------------
ATEMPTAB$

SQL>


Ross Leishman

Exclamation Disclaimer: Anyone who does this is a twit.
Re: select single record not using where clause [message #244799 is a reply to message #244796] Thu, 14 June 2007 02:44 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
rleishman wrote on Thu, 14 June 2007 09:36


Exclamation Disclaimer: Anyone who does this is a twit.


http://www.orafaq.com/forum/fa/449/0/

MHE
Re: select single record not using where clause [message #244820 is a reply to message #244796] Thu, 14 June 2007 03:13 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rleishman wrote on Thu, 14 June 2007 09:36
Exclamation Disclaimer: Anyone who does this is a twit.

./fa/1581/0/

Regards
Michel
Previous Topic: Pound (£) sign in Oracle 8i
Next Topic: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
Goto Forum:
  


Current Time: Sat Dec 10 18:24:23 CST 2016

Total time taken to generate the page: 0.09531 seconds