CODEWITHSUNDEEP
sqlpostgresql
St.Antario
St.Antario

Reputation: 27375

Is it possible to specify limit via where clause in postgresql?

Is it possible? I mean

Select * from tbl limit 100;

I want to replace with a query like

Select * from tbl WHERE ...some_condition...

Upvotes: 1

Views: 673

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Do you want the where clause only applied to the first hundred rows? If so, use a subquery:

select . . .
from (select t.*
      from table t
      limit 100) t
where . . .

This will return less than 100 rows (presumably not all rows match the condition). Also, when using limit, you should have an order by condition.

Upvotes: 2

Andomar
Andomar

Reputation: 238048

You could use row_number() for that:

select  *
from    (
        select  row_number() over () as rn
        ,       *
        from    YourTable
        ) as SubQueryAlias
where   rn <= 100

If you have a specific ordering in mind, you can add an order by clause:

select  row_number() over (order by date_column) as rn

Upvotes: 2

9000
9000

Reputation: 40884

You cannot put a limit clause inside a where clause. But you can of course append a limit clause after the where clause.

Upvotes: 1

Related Questions