Using limit 1 in Postgres

Question

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

;

Gordon Linoff · Accepted Answer · 2020-11-12 17:32:25Z

2

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;

answered Nov 12, 2020 at 17:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fahmi · Accepted Answer · 2020-11-12 17:32:14Z

0

For sql server you need to use top

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

answered Nov 12, 2020 at 17:32

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Collectives™ on Stack Overflow

Using limit 1 in Postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related