D3_GXT_Java D3_GXT_Java - 30 days ago 8
SQL Question

How to write a query to allow null in minimum function

I need to write a query to get minimum values for a column from a table and if the value is null then I want to include that row. I wrote following query but it ignores the null values. How I can modify this query to include null values in the result?

select * from TABLE where COLUMN = (select min(COLUMN) from TABLE );


If the table is like below

|ID | VALUE | NAME
101 1 John
101 null John
102 1 Bill
103 1 Tina
103 null Tina
104 null James


Result Should be

|ID | VALUE | NAME
101 1 John
102 1 Bill
103 1 Tina
104 null James

Answer Source

You need distinct on:

with my_table(id, value, name) as (
values
    (101, 1,    'John'),
    (101, null, 'John'),
    (102, 1,    'Bill'),
    (103, 1,    'Tina'),
    (103, null, 'Tina'),
    (104, null, 'James')
)

select distinct on (id) *
from my_table
order by id, value

 id  | value | name  
-----+-------+-------
 101 |     1 | John
 102 |     1 | Bill
 103 |     1 | Tina
 104 |       | James
(4 rows)

Distinct on is a fantastic feature specific for Postgres. An alternative in other RDBMS may be:

select t.id, t.value, t.name
from my_table t
join (
    select id, min(value) as value
    from my_table
    group by id
) u on u.id = t.id and u.value is not distinct from t.value;

Note, you should use is not distinct from because value may be null.