Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Say I have a table with the following information (sorted here for display purposes)

taskid   |  reference   |  centreid  |  date_out
---------------------------------------------------
10001    | 'IX#001323'  |         1  | '2012-09-28'
10022    | 'IX#001323'  |         2  | '2012-10-04'
10032    | 'IX#001544'  |         1  | '2012-10-09'
10046    | 'IX#001666'  |         1  | '2012-10-10'
10056    | 'IX#001666'  |         3  | '2012-10-13'
10078    | 'IX#002100'  |         2  | '2012-10-23'
10098    | 'IX#002100'  |         1  | '2012-11-01'

I want to select the groups of rows that have the same reference where the first occurrence belonged to a particular centreid, but where the second occurrence was within a particular date range. For example, I want to find all taskids that have the same reference where the second date falls in October but the first centreid is 1, the results should be:

taskid   |  reference   |  centreid  |  date_out
---------------------------------------------------
10001    | 'IX#001323'  |         1  | '2012-09-28'
10022    | 'IX#001323'  |         2  | '2012-10-04'
10046    | 'IX#001666'  |         1  | '2012-10-10'
10056    | 'IX#001666'  |         3  | '2012-10-13'

It's possible that there are more than two taskids with the same reference, in which case, I am always interested in the last date and the second to last centreid (when ordered by date_out). Each time I write a query it ends up being very complex and still not returning exactly the rows I want.

I have:

SELECT taskid
FROM tasks t1
WHERE (EXISTS (SELECT 1
               FROM tasks t2
               WHERE t1.taskid <> t2.taskd
                 AND t1.reference = t2.reference))
 AND
    -- STUCK 
    -- Unsure how to select amongst remaining rows where max(date) between two dates

Thanks in advance for any help. I'm using PostgreSQL 9.1 if that makes any difference...

Update (2012-11-10)

I have used the answer provided by Erwin, but the self join in CTE d took a long time to execute, so I split it up into two different CTEs. This provided an overwhelming performance improvement. Erwin's original answer executed in around 120 seconds (choking on CTE d) whereas the below executes in just under 2 seconds.

... snip ...
,d AS (
   SELECT reference, grp
   FROM   c
   WHERE  c.rn = 1 AND c.date_out BETWEEN '2012-10-01' AND '2012-10-31'
   )
,e AS (
   SELECT reference, grp
   FROM   c
   WHERE  c.rn = 2 AND centreid = 1
   )
SELECT b.taskid, b.reference, b.centreid, b.date_out
FROM   b
JOIN   d USING (reference, grp)
JOIN   e USING (reference, grp)
ORDER  BY reference, date_out
share|improve this question

1 Answer 1

up vote 2 down vote accepted

Get qualifying rows only

One way ..

WITH x AS (
   SELECT *
         ,row_number() OVER (PARTITION BY reference ORDER BY date_out DESC) AS rn
   FROM   tbl
   )
, y AS (
   SELECT *
   FROM   x
   WHERE  x.rn = 1
   AND    date_out >= '2012-10-01'
   AND    date_out <  '2012-11-01'
   )
, z AS (
   SELECT x.*
   FROM   x
   JOIN   y USING (reference)
   WHERE  x.rn = 2
   AND    x.centreid = 1
   )
SELECT y.taskid, y.reference, y.centreid, y.date_out
FROM   y
JOIN   z USING (reference)

UNION  ALL
SELECT taskid, reference, centreid, date_out
FROM   z
ORDER  BY reference, date_out;

Another way:

WITH x AS (
   SELECT *
         ,row_number() OVER (PARTITION BY reference ORDER BY date_out DESC) AS rn
   FROM   tbl
   )
,y AS (
    SELECT x.*, y.taskid AS taskid2, y.centreid AS centreid2, y.date_out AS date_out2
    FROM   x
    JOIN   x y USING (reference)
    WHERE  x.rn = 1
    AND    x.date_out >= '2012-10-01'
    AND    x.date_out <  '2012-11-01'
    AND    y.rn = 2
    AND    y.centreid = 1
    )
SELECT y.taskid, y.reference, y.centreid, y.date_out
FROM   y

UNION  ALL
SELECT y.taskid2, y.reference, y.centreid2, y.date_out2
FROM   y
ORDER  BY reference, date_out;

I'd expect the second one to be faster. Depends on your data distribution. Test with EXPLAIN ANALYZE.

Get all rows for qualifying references

WITH x AS (
   SELECT *
         ,row_number() OVER (PARTITION BY reference ORDER BY date_out DESC) AS rn
   FROM   tbl
   )
,y AS (
    SELECT reference
    FROM   x
    JOIN   x y USING (reference)
    WHERE  x.rn = 1
    AND    x.date_out >= '2012-10-01'
    AND    x.date_out <  '2012-11-01'
    AND    y.rn = 2
    AND    y.centreid = 1
    )
SELECT *
FROM   tbl
JOIN   y USING (reference)
ORDER  BY reference, date_out;

-> sqlfiddle

Answer to follow-up in comment

Separate groups if more than 30 days between entries.

WITH a AS (
   SELECT *
         ,lag(date_out) OVER (PARTITION BY reference ORDER BY date_out DESC) AS last_date
         ,CASE WHEN date_out > 
                   (lag(date_out) OVER (PARTITION BY reference ORDER BY date_out DESC) - 30)
               THEN 0 ELSE 1
          END AS step
   FROM   tbl
   )
,b AS (
   SELECT *
         ,sum(step) OVER (PARTITION BY reference ORDER BY date_out DESC) AS grp
   FROM a
   )
 ,c AS (
   SELECT *
         ,row_number() OVER (PARTITION BY reference, grp ORDER BY date_out DESC) AS rn
   FROM b
   )
,d AS (
   SELECT reference, grp
   FROM   c
   JOIN   c d USING (reference, grp)
   WHERE  c.rn = 1
   AND    c.date_out >= '2012-10-01'
   AND    c.date_out <  '2012-11-01'
   AND    d.rn = 2
   AND    d.centreid = 1
   )
SELECT b.taskid, b.reference, b.centreid, b.date_out
FROM   b
JOIN   d USING (reference, grp)
ORDER  BY reference, date_out

-> sqlfiddle

But while pure SQL is a beaut .. I would solve this procedurally in a plpgsql function. Very similar to this recent answer on SO. Would be faster, because it can be done in a single table scan.

share|improve this answer
    
This is great, thanks for your help. This works well when there are only two occurrences with the same reference, but there are also some instances where there are 3 or 4 taskids with the same reference. I understand how your query operates but at the same time I don't know how to expand it to include the extra related rows. The second one is definitely faster. The first averages 800ms per run whereas the second only 500ms. –  dreamlax Nov 5 '12 at 3:08
    
@dreamlax: So you want all rows with a reference that qualifies? Right? That's even simpler. Added a query. –  Erwin Brandstetter Nov 5 '12 at 3:23
    
Well, it's a bit more complicated but I wanted to at least try and sort out the complications myself. Basically, I want to group all rows with the same reference where the date_out between each is no more than 30 days. I asked a question about this previously here. So, if there is more than 30 days between two rows with the same reference then it should be treated as a separate "group". –  dreamlax Nov 5 '12 at 3:25
    
Ah! Now I get it... your last edit makes sense. I really need to read a book about this stuff. Do you have any recommendations? From your query and the query from the previous question I asked, I should be able to concoct the query I want. Thanks again for your help! –  dreamlax Nov 5 '12 at 3:27
    
@dreamlax: I have a better idea for this purpose .. actually two ideas. Added more to the answer. –  Erwin Brandstetter Nov 5 '12 at 4:08

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.