CODEWITHSUNDEEP
sqlpostgresqlsql-limit
akki
akki

Reputation: 91

Using limit 1 in Postgres

I have the following sql query, which runs fine on MYSQL but gives error with Oracle and SQL Server because of the last limit 1 clause. My question is just - Is this limit 1 clause valid as per PostgreSQL and can I expect this to run successfully on Postgres?

select customer_number 
from
(select 
customer_number,
count(*) 
from
orders
group by 
customer_number 
order by count(*) desc limit 1) a

;

Upvotes: 0

Views: 802

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

limit is supported by some databases, but not all. The SQL standard would be:

select customer_number
from orders
group by customer_number 
order by count(*) desc 
offset 1 row fetch first 1 row only;

Upvotes: 2

Fahmi
Fahmi

Reputation: 37473

For sql server you need to use top

select top 1 customer_number,count(*) 
from orders
group by customer_number 
order by count(*) desc

Upvotes: 0

Related Questions