Elegant syntax for multi-column "cascading" constraint in SQL?

Question

I am constructing a query that will naturally return a result that has values for several columns, as well as some integer-valued grouping columns (indicating whether a particular value column was subtotalled but that's not in itself relevant to my question). I want to put a constraint on this query's grouping columns, that only accepts rows where grouping column n is non-zero if all the grouping columns 1 - (n-1) are also non-zero.

It seems like there ought to be a nice way to express this in SQL, but all I can come up with so far is the rather cumbersome

where ((grp1 = 0 AND grp2 = 0 AND grp3 = 0 AND ... AND grpn = 0)
    or (grp1 = 1 AND grp2 = 0 AND grp3 = 0 AND ... AND grpn = 0)
    or (grp1 = 1 AND grp2 = 1 AND grp3 = 0 AND ... AND grpn = 0)
    ...
    or (grp1 = 1 AND grp2 = 1 AND grp3 = 1 AND ... AND grpn = 1)

which is harder to understand the concept from if you're reading it, looks like it would be harder for a SQL engine to come up with a decent optimisation, and will generally just clog up logs as well, burying the important bits of the SQL in noise.

Since this concept seems not too unusual, I'm hoping there is a better way to express it that will generally be more concise and hopefully will be more performant on the server itself (assuming my naive version isn't going to be that efficient).

Answer 1

You say that the values are integers. Can they be negative? If not, you could just use a set of comparisons:

where (grp1 >= grp2 and grp2 >= grp3 ...)

Edit: actually, I'm also assuming here that non-zero values have to be 1... but the principle could still be applied if that's not the case, I'd imagine.

Answer 2

If (n) is fixed, then Ben's answer seems to be the best.

If your data is normalised and (n) is variable, I'd assume you have data something like this...

data_fk | grp_id | flag

Then you want to search for data_fk's where the flags for the various grp_ids conforms to therule you set out. The rule could (in my mind) be restated as...

The (highest group_id where the flag = 1) should be the grp_id before the (lowest group_id where the flag = 0), or all flags are 1, or all flags are 0.

SELECT
    data_fk
FROM
    a_table
GROUP BY
    data_fk
HAVING
    ISNULL(MIN(CASE WHEN flag = 0 THEN grp_id ELSE NULL END), MAX(grp_id) + 1)
    =
    MAX(CASE WHEN flag = 1 THEN grp_id ELSE 0 END) + 1