Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:

I have 4 tables in Postgres with the following organization

reads:
id -- primary key
value -- integer
read_datetime -- datetime
patient_id -- integer
reader_id -- integer

readers:
id -- primary key

patient_readers:
id -- primary key
patient_id -- integer
reader_id -- integer
issuance_datetime -- datetime
unassignment_datetime -- datetime
issued_value -- integer

patients:
id -- integer

I want to create a query where I can get the most recent issuance information for a particular read. So far I have the following query

SELECT
value,
read_datetime,
issued_value,
issuance_datetime,
unassignment_datetime
FROM "reads"
INNER JOIN "readers" ON "readers"."id" = "reads"."reader_id"
INNER JOIN "patient_readers" ON "patient_readers"."reader_id" = "readers"."id" AND patient_readers.patient_id = reads.patient_id 
INNER JOIN patientsON patients.id = reads.patient_id
ORDER BY reads.patient_id , read_datetime, issuance_datetime

This produces output where a group of rows are the same for all information other than the issuance information (issuance_datetime, unassignment_datetime, and issued_value) as I would expect. I would like to aggregate the issuance information with respect to readers.id and patients.id, and then I would only like the one set of issuance information where

read_datetime > issuance_datetime AND (
    (unassignment_datetime IS NOT NULL AND read_datetime < unassignment_datetime) OR
    (NEXT(issuance_datetime) IS NOT NULL AND read_datetime < NEXT(issuance_datetime) OR
    (NEXT(issuance_datetime) IS NULL)

Here I'm making up the function NEXT() but basically I want to look ahead one row with respect to the window. I also want to do a WHERE clause in the window (or so it seems). I have just started reading about window functions, so a lot of this is new to me (and I'm not positive that window functions are what I want) so I hope this is solvable within Postgres.

Thanks

share|improve this question

1 Answer 1

up vote 1 down vote accepted

Check out the lead() function. That one can look at the "next" row in the window.

http://www.postgresql.org/docs/current/static/functions-window.html

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.