clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2389311 fiddles created (37795 in the last week).

CREATE TABLE product(id int, quantity int); INSERT INTO product VALUES (1, 1) , (2, 666) , (3, NULL) , (4, 33) , (5, 666) , (6, -1) , (7, 666) ;
7 rows affected
 hidden batch(es)


CREATE TABLE product1(id int, quantity int); INSERT INTO product1 VALUES (1, NULL) , (2, NULL) , (3, NULL) ;
3 rows affected
 hidden batch(es)


TABLE product;
id quantity
1 1
2 666
3
4 33
5 666
6 -1
7 666
 hidden batch(es)


SELECT id, quantity FROM product ORDER BY quantity DESC NULLS LAST FETCH FIRST 1 ROWS WITH TIES;
id quantity
5 666
2 666
7 666
 hidden batch(es)


SELECT id, quantity FROM product ORDER BY quantity DESC NULLS LAST FETCH NEXT 1 ROW WITH TIES;
id quantity
5 666
2 666
7 666
 hidden batch(es)


-- ONLY instead of WITH TIES returns exactly 1 row (if any): SELECT id, quantity FROM product ORDER BY quantity DESC NULLS LAST FETCH NEXT 1 ROW ONLY;
id quantity
2 666
 hidden batch(es)


SELECT id, quantity FROM product WHERE quantity IS NOT DISTINCT FROM (SELECT MAX(quantity) FROM product);
id quantity
2 666
5 666
7 666
 hidden batch(es)


SELECT id, quantity FROM ( SELECT *, rank() OVER (ORDER BY quantity DESC NULLS LAST) AS rnk FROM product ) sub WHERE rnk = 1;
id quantity
5 666
2 666
7 666
 hidden batch(es)


-- even works with all NULL values: SELECT id, quantity FROM product1 WHERE quantity IS NOT DISTINCT FROM (SELECT MAX(quantity) FROM product1);
id quantity
1
2
3
 hidden batch(es)


SELECT id, quantity FROM ( SELECT *, rank() OVER (ORDER BY quantity DESC NULLS LAST) AS rnk FROM product1 ) sub WHERE rnk = 1;
id quantity
1
2
3
 hidden batch(es)


-- your original doesn't SELECT id, quantity FROM product1 WHERE quantity = (SELECT MAX(quantity) FROM product1);
id quantity
 hidden batch(es)