Elegant approach to fetch the first value from each group without using outer query

Question

I am trying to fetch the first value from each of the groups in my data. However I don't like to use the outer query/with clause. Can you let me know how can I write this in a elegant way without using outer query?

I have used outer query to fetch the first value from each group. However, Can you please help me write this in elegant manner within the same sql. Is there any function like Max, Min that will give us the first value so that I don't have to write this outer query

select * 
from (    
  select subject_id,hadm_id,
         rank() OVER (PARTITION BY subject_id ORDER BY row_id) AS BG_CG_number 
  from labevents
  where itemid in ('50809','50931','51529') 
    AND valuenum > 110 
    and hadm_id is not null
) t1 
where t1.bg_cg_number = 1

Please find the screenshot below for current and expected output

Answer 1

There is nothing wrong with the derived table (aka sub-query).

Postgres' proprietary distinct on () will achieve the same and is usually faster than using a window function (that's not because of the derived table, but because of the window function):

Quote from the manual

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above).

Note that the “first row” of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first.

So you query can be rewritten to:

select distinct on (subject_id) subject_id, hadm_id
from labevents
where itemid in ('50809','50931','51529') 
  AND valuenum > 110 
  and hadm_id is not null
order by subject_id, row_id;