Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2010
    Posts
    2

    Unanswered: counting repetition

    Hi All,

    I have a table with primary keys on the left, and repeating values on the right. I am trying to keep the repetition of EACH value 4 or under. Im not sure how to make this restriction.

    Example:
    ‎1, 101
    2, 101
    3, 101
    4, 101
    5, 102
    6, 102
    7, 102
    8, 103
    9, 102
    10, 103
    11, _

    since 101 and 102 have already reached 4repetitions i dont want them repeating again in 11. but 103 has only repeated twice so it would be allowed to be entered into 11.

    Thank you all in advance.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Something like this (not tested):
    Code:
    SELECT * 
    FROM ( 
      SELECT pk_column, 
             count(*) over (partition by the_other_column order by pk_column) as cnt
      FROM the_table_with_no_name
    )
    WHERE cnt <= 4
    ORDER BY pk_column;

  3. #3
    Join Date
    Nov 2010
    Posts
    2
    Quote Originally Posted by shammat View Post
    Something like this (not tested):
    Code:
    SELECT * 
    FROM ( 
      SELECT pk_column, 
             count(*) over (partition by the_other_column order by pk_column) as cnt
      FROM the_table_with_no_name
    )
    WHERE cnt <= 4
    ORDER BY pk_column;
    Hi Shammat, thanks for the reply.

    I think I might have not been too clear with what I'm looking for. I'm not looking to have a select statement that gives me results with no repetitions, but instead i am looking to create a constraint(like a check constraint possibly) that doesnt allow the user to enter a certain same value if it has already showed up 4 times.

    So, since we already have 101 and 102 showing up 4 times in my example above, then the constraint should not allow users to enter 101 or 102 in the 2nd column again.

    Thank you.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can do this with an indexed view.

    Create a view that includes a count of records grouped by Value, bind it to your table (required for indexed views), and then put on constraint on the count column requiring it to be less than or equal to four.

    This will prevent any particular value occurring in your table more than four times.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •