CODEWITHSUNDEEP
plsql
vr552
vr552

Reputation: 301

How to select max() value of some of the selected rows?

There are two types of rows in document_files table - that have active=1 or active=0. Each document (document_id) can have multiple rows of each type. The goal is to for every document select all rows that have active=1, but only the latest one of all rows that have active=0.

select *
from documents d
inner join document_files df on df.document_id = d.id
where ...
-- df.active = 1, select them all
-- or df.active = 0, only select max(df.last_stamp) of all df.active=0 rows for this df.document_id

How to achieve this?

I'd use union and group by, but the problem is that a lot of columns are returned, and it wouldn't be optimal to group them all.

Upvotes: 0

Views: 38

Answers (1)

Ali Fidanli
Ali Fidanli

Reputation: 1372

This section only get the document files with no active record and after that using max () gets the most recent record for the item:

select document_id
from (select * from document_files f where f.document_id not in
(select document_id from document_files where active = 1)) dff where dff.last_stamp = 
(
select max(i.last_stamp) from document_files i where i.document_id = dff.document_id
)

My final sql :

  select *
    from documents d
    inner join document_files df on df.document_id = d.id
    where df.active = 1 or 
    df.document_id in
     (
    select document_id
    from (select * from document_files f where f.document_id not in
    (select document_id from document_files where active = 1)) dff where dff.last_stamp = 
    (
    select max(i.last_stamp) from document_files i where i.document_id = dff.document_id
    )
    )

Please add some sql fiddle next time so we can test the result

Upvotes: 1

Related Questions