count(*) over (partition by the_other_column order by pk_column) as cnt
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.
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.