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!

asked 22 Apr, 11:56

dharrel's gravatar image

dharrel
1317815
accept rate: 0%


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

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

permanent link

answered 22 Apr, 12:27

John%20Smirnios's gravatar image

John Smirnios
10.9k392145
accept rate: 37%

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 )

(22 Apr, 12:32) John Smirnios

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.

(22 Apr, 12:38) dharrel

I'd guess the folowing should do:

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

permanent link

answered 22 Apr, 12:29

Volker%20Barth's gravatar image

Volker Barth
37.7k347514780
accept rate: 34%

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.

(22 Apr, 12:40) dharrel

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

(22 Apr, 12:41) Volker Barth
Replies hidden

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)

(22 Apr, 12:57) John Smirnios

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...)

(22 Apr, 15:03) Volker Barth

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

permanent link

answered 23 Apr, 05:10

Andr%C3%A9%20Schild's gravatar image

André Schild
106227
accept rate: 0%

Thanks. Great ideas from all!

(23 Apr, 12:27) dharrel
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×36

question asked: 22 Apr, 11:56

question was seen: 260 times

last updated: 23 Apr, 12:27