CODEWITHSUNDEEP
sqloracle-databaseselectcount
user999379
user999379

Reputation: 411

How to get rows from a table if total rows are more than 10 in Oracle?

I want to write a Oracle based query where I can choose if I want to see the results. Let's say:

SELECT *
FROM table
//when there are more than 10 rows

How can I do this?

Upvotes: 2

Views: 2851

Answers (6)

Florin Ghita
Florin Ghita

Reputation: 17643

Best speed:

select * from table
where 10=(select count(*) from
          table
          where rownum <11)

:)

UPDATE: Because there are suspicions that I claim something that is not true, here some tests: In SQL Developer(keep in mind that select * from table will offer only first 50 rows, but count(*) read all requested rows) The table has no indexes.

select
count(*) from
table

22074412 rows

3.16 seconds

select * from table where 10 = 
(select
count(*) from
table
where rownum <11
)

0.051 seconds

select * from table where 10 < 
(select
count(*) from
table
)

3.39 seconds

select count(*) from table where 10 < 
(select
count(*) from
table
)

7.69 seconds

select count(*) from table where 10 = 
(select
count(*) from
table
where rownum <11
)

3.42 seconds

Cause: Subquery with rownum is faster (it reads not the entire table)

Upvotes: 8

Daniel Haviv
Daniel Haviv

Reputation: 1036

if you would like to avoid double scans and you have valid statistics you can 

select * from table a, all_tables b
where    b.num_rows > 10
and      b.table_name = 'table';

Upvotes: 1

CloudyMarble
CloudyMarble

Reputation: 37566

select * from YourTable where (select count(*) from YourTable ) > 10

Upvotes: 1

user359040
user359040

Reputation: 

select * from table
where 10 < (select count(*) from table)

Upvotes: 15

Barry Kaye
Barry Kaye

Reputation: 7761

This should do it for you:

SELECT * FROM [table] WHERE (SELECT COUNT(1) FROM [table]) > 10

Upvotes: 1

Ruben
Ruben

Reputation: 699

DECLARE @Var int;
SET @Var = SELECT COUNT(*) FROM [somewhere]

IF @Var > 10
BEGIN
    SELECT * FROM [somewhere]
END

You mean something like that? Or just how to use the where clause?

SELECT * 
FROM [somewhere]
WHERE (SELECT COUNT(*) FROM [somewhere]) > 10

Upvotes: 2

Related Questions