PostgreSQL SELECT between records one attribute with conditional assesment of a second attribute

Question

I have a dataset of field samples whose attributes include: a sample name, a timestamp of analysis (unique key), analysis result, date sampled, and a Boolean attribute 'rerun' that describes if the record is the result of a second analysis of the same sample. This means that there can be multiple records with the same sample name and sample date, but in that case, the second record (rerun = TRUE) is the preferred result for the selection.

name timestamp analysisResult rerun dateSampled

I have attempted to use the CASE function, but I think I am missing something simple. I'm trying to put it as an element of the WHERE clause, but that is not working as is.

For example, I'm trying something like:

    SELECT name,analysisResult
    FROM sciencyTable
    WHERE date = '12/02/11',
    AND (some working CASE function)
    ORDER BY time;

Any hints would be appreciated. Thank you.

dbenhur · Accepted Answer · 2012-11-09 18:03:52Z

1

One solution to your query is to outer join against rerun samples and coalesce:

select
  firstruns.name
, coalesce(reruns.analysisResult, firstruns.analysisResult) as analysisResult
from
(select name, dateSampled, analysisResult
 from sciencyTable
 where not rerun
) as firstruns
left join
(select name, dateSampled, analysisResult
 from sciencyTable
 where rerun
) as reruns
using (name,dateSampled)

If you have multiple reruns for given name,date and want to pick the latest, you should try windowing functions

answered Nov 9, 2012 at 18:03

dbenhur

20.5k4 gold badges51 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

damian Over a year ago

Thank you, dbenhur, I will give that a try. Thanks also for the link, looking at it now. Cheers.

Collectives™ on Stack Overflow

PostgreSQL SELECT between records one attribute with conditional assesment of a second attribute

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related