I am trying to count the continuous repeated value for a field in order to obtain the maximum repeated occurrences for each person in the table. eg:

Time         person        Result
08:12:36     Dave          Heads
08:12:37     Dave          Heads
08:12:39     Sue           Tails
08:12 41     Dave          Tails
08:12:42     Dave          Heads
08:12:44     Dave          Heads
08:12:45     Sue           Heads
08:12:46     Sue           Heads
08:12:47     Sue           Tails
08:12:48     Dave          Heads
08:12:49     Dave          Heads
08:12:50     Dave          Tails
08:12:51     John          Heads

Result should look like this

Dave     4
Sue      2
John     1

The count for Dave would be 4 as between 08:12:42 and 08:12:49, 4 Heads options occur for Dave before Dave gets a Tails option.

  • 1
    Why Dave is 4 and not 3? Dec 10 '14 at 9:39
  • Or should Dave be 6? Dec 10 '14 at 9:42
  • 1
    @Colin'tHart I think with "continuous" they mean consecutive. It looks like a gaps-and-islands problem. Dec 10 '14 at 9:43
  • @Ypercube Probably. Dec 10 '14 at 9:44
  • @ypercube The count for Dave would be 4 as between 08:12:42 and 08:12:49, 4 Heads options occur for Dave before Dave gets a Tails option
    – chaos
    Dec 10 '14 at 10:14
SELECT person, max(ct) AS max_ct
   SELECT person, count(*) AS ct
   FROM  (
      SELECT person, result
           , row_number() OVER (PARTITION BY person ORDER BY time)
           - row_number() OVER (PARTITION BY person, result ORDER BY time) AS grp
      FROM   tablex
      ) sub1
   GROUP BY person, result, grp
   ) sub2
GROUP  BY person
ORDER  BY max(ct) DESC, person;

SQL Fiddle.

Result exactly as desired.

After forming groups (grp) of consecutive pairs (person, result), the count has to be per pair and group (person, result, grp), not just per person and group (person, grp). There can (and will) be one group with the same group number per person and result, those have to be counted separately.

Also, it's not efficient to use CTEs here. In Postgres, performance is generally superior for subqueries, since CTEs are optimization fences that always materialize the result. Use CTEs only where they are actually necessary.

  • Correct, I missed that. Dec 10 '14 at 14:46
  • @erwin brandstetter Yes. Thank you. Has resolvled the issue of the first value in results affecting query outcome.
    – chaos
    Dec 10 '14 at 14:49
  • 1
    @Peter: It wasn't actually the first position of the row that affected the outcome. It just so happened that due to the additional row a second group with grp = 2 was formed for the same person (one for each result) and both were lumped together by mistake. Dec 10 '14 at 14:53
  • @erwin brandstetter Understand now better after your explanation. Is there any tool in sql that allows "step" function like you get in programming? This would help much when evaluating complex queries.
    – chaos
    Dec 10 '14 at 14:57
  • @Peter: There are many tools. You would have to define more closely what a "step" should be. Remember that SQL is a declarative language (mostly), not a procedural language, before talking about "steps". If you need steps in the procedural sense I suggest PL/pgSQL. Example. Start a new question with the necessary details, if you still have a question to ask. Dec 10 '14 at 15:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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