SQL Where clause greater/less than multiple numeric columns

[dhkom]

I never thought I'd be asking an SQL question here. However today is the day!

Say I have a table with numeric columns A, B populated as follows. We can think of column A being a foreign key to a parent table, and column A and B together being the primary key of its child table:

A  B
1  1
1  2
2  1
2  2
3  1
3  2

I would like a SQL Select to return all records after, for example, (2, 1). I.e. (2, 2), (3, 1), and (3, 2).

Obviously the following won't work (it won't return (3, 1)):

select A, B from mytable when A > 2 and B > 1

I wish there were a way to write "A > 2 and B > 1" in a way that indicates B is "is a breakdown" of A, for lack of a better way to express it.

Of course I could create a derived column with the two numbers concatenated together, padded with enough zeros to accomodate maximum number size. Something like:

A  B  AandB
1  1  0101
1  2  0102
2  1  0201
2  2  0202
3  1  0301
3  2  0302

... Then I could write the SQL I want as:

select A, B from mytable when AandB > '0201'

However it would be wonderful if I could write a Where clause operating on the original numbers.

Maybe it would have been best to avoid multiple numeric columns making up a child table's key, although I'm not sure avoiding such would always eliminate the need for what I'm asking about.

This has been a tough one to Google search for solutions to. Thoughts and ideas are welcome!

[johnsmirnios]

That would be a tuple comparison. The following would work:

WHERE (A = 2 AND B > 1) OR A > 2

[johnsmirnios]

I'm a little fuzzy on arrays but the following might also work and is easily extended to more columns:

WHERE ARRAY( A, B ) > ARRAY( 2, 1 )

[dhkom]

Thanks, John. I probably should have figured that out too - sometimes we're just blocked. I've not used this ARRAY syntax and will definitely investigate.

[André_Schild]

If your know the number range of your columns, then perhaps just multiply the first one by for example 10000 and then filter n that one.

Something like:

select A, B from mytable where A*10000+B  > 20001

Of course only works if you don't run into a numeric overflow, and the array solution already mentioned is elegant. I'm just wondering how both solutions compare on the performance level for large tables

[dhkom]

Thanks. Great ideas from all!

[VolkerBarth]

I'd guess the folowing should do:

select A, B from mytable where A = 2 and B > 1 or A > 2;

[dhkom]

Thanks, Volker. I probably should have figured that out too - sometimes we're just blocked. Thinking more about this, multi-level numeric primary keys should probably be avoided in favor of surrogate keys. This may have eliminated my need here. However the database in question is well established as it is.

[VolkerBarth]

The somewhat shorter answer for those aware of the higher precedende of AND vs. OR...:)

[johnsmirnios]

It's fine to rely on precedence but I have had too many first hand experiences in multiple languages where the author clearly assumed the wrong precedence 🙂 There's no mistaking the order of operations when the parentheses are given.

My favourite one from C/C++ is

bool flag_is_set = flags&SOME_FLAG_MASK != 0;

It does not do what the author intended. It does the following:

bool flag_is_set = flags & (SOME_FLAG_MASK != 0);

which will always return zero if SOME_FLAG_MASK is zero or the low bit of flags otherwise.

I've also seen

if( a &&

b || c &&

d )

{ ... }

and they clearly meant (b || c)

[VolkerBarth]

Well, I do get the point. On the other hand, I certainly dislike it when each and every condition is put into parantheses, as some tools do... It just makes it harder to read, and usually there is common knowledge of precedence rules. (Bitwise operators are less common, for sure...)