How to get row(s) with max or null value in Postgres

Question

I would like get all rows with h_id max value or NULL if present there. For example result for table below will be:

fid |h_id |
--------|--------|
13374976| |
13377411|66666666|

I tried to write select statement but it returns only max values. How should I extend subselect in this statement for getting correct values?

select
 a.fid,
 a.h_id
from
 my_table a
where
 a.h_id = (
 select
 MAX(sub.h_id)
 from
 my_table sub
 where
 sub.FID = a.FID)
group by
 a.fid,
 a.h_id
order by
 a.fid

fid |h_id |
--------|--------|
13374976|58280198|
13374976|58372885|
13374976| |
13377411|58280199|
13377411|66666666|

• Dan Guzman: Is your question only related to PostgreSQL? If so, remove the Microsoft SQL Server tag.

Answer 1

In order to answer your question, I did the following (fiddle available here):

CREATE TABLE test
(
 fid INTEGER NOT NULL,
 h_id INTEGER,
 CONSTRAINT h_id_uq UNIQUE (h_id)
);

populate the table - added a couple of extra records for testing purposes.

INSERT INTO test VALUES 
(13374976, 58280198),
(13374976, 58372885),
(913374976, NULL ),
(23, NULL),
(913377411, 58280199),
(13377411, 66666666),
(13377411, 77777777);

And the ran the following query:

SELECT fid, h_id FROM test
WHERE h_id = 
(
 SELECT MAX(h_id)
 FROM test
)
UNION
SELECT fid, h_id
FROM test 
WHERE h_id IS NULL;

Result:

fid h_id
23 NULL
13377411 77777777
913374976 NULL

This provides the answer you require - there is an assumption that h_id is UNIQUE or else you'll have to do a GROUP BY fid - but that's a different question.

In future, could you please provide a fiddle with your table structures and your (presumably) sample data - it makes life much easier for those trying to help you and eliminates duplication of effort as well as providing a Single Source of Truth.

Also, although it doesn't make much difference in this case, could you also please provide your PostgreSQL version as a tag or in the question itself - it can be helpful!

p.s. welcome to the forum!