homepage Welcome to WebmasterWorld Guest from 54.166.113.249
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
selecting where list elements are in a range
possible to do in a query or need to loop?
LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 565 posted 6:02 pm on Jun 5, 2006 (gmt 0)

I have a MS SQL 2000 table with the following structure- 1st & second columns are integers (with 1st < 2nd), and 3rd column is a character string. Example:
1,5,'Range1'
10,11,'Range2'
13,35,'Range3'

I have a list of numbers that I need to check if they are in one of the ranges in the table, and if so grab the 3rd column.

For example, if the list was "1,6,11,16,21" I would want to return "'Range1','Range2'"
Actuallly, I would be even happier if it just verified that elements "1,11" returned matches.

Is there an easy way to do this within a single query? Or do I have to loop over the list and do a query for each element in the list? The table has about 15,000 records and the lists will run 4,000-8,000 elements. So I would much prefer to figure out some way to do it with just 1 database call instead of 1 call for each element!

 

arran

5+ Year Member



 
Msg#: 565 posted 6:36 pm on Jun 5, 2006 (gmt 0)

For example, if the list was "1,6,11,16,21" I would want to return "'Range1','Range2'"

Due to the occurrence of 16 and 21, wouldn't "Range3" also be returned.
If not, could you please explain the logic behind your example/schema.

Thanks,
arran.

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 565 posted 6:41 pm on Jun 5, 2006 (gmt 0)

Yes, sorry about that- it should have been "Range1,Range3"

arran

5+ Year Member



 
Msg#: 565 posted 6:45 pm on Jun 5, 2006 (gmt 0)

What about "Range2" given the occurrence of 11?

Sorry to nitpick, i'm just making sure I understand what you're trying to achieve :)

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 565 posted 8:22 pm on Jun 5, 2006 (gmt 0)

I'm trying to achieve a coherent sentence, and it's not working out today. :)

Let's try this again...

If the test list was "1,6,11,16,21" I'd like to get back that "1,11,16,21" were found to be in valid ranges. Or alternatively, that "6" was found not to be within any of the ranges.

Demaestro

WebmasterWorld Senior Member demaestro us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 565 posted 8:37 pm on Jun 5, 2006 (gmt 0)

Does the list you are wanting to check to see if the elements fall into range live in a table or get passed in as an incoming arg?

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 565 posted 8:46 pm on Jun 5, 2006 (gmt 0)

It could be setup either way. The list is coming from a subset of another query.

Demaestro

WebmasterWorld Senior Member demaestro us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 565 posted 10:18 pm on Jun 5, 2006 (gmt 0)

LIA I keep trying to write this out and I keep mstalling. So far I am thinking that a case statement is the right track. Although I am think I am stuck where you might be and that is doing it without looping through the values you want to check. I presume you want 1 record per value? Returning soething like:

value range
1 range1
2 range1
6 No Range Found

Type of thing.

I was thinking if you could loop through the logic to dynamically create SQL that you could run you could create a big union statement that would return the data all in one record set.

So something like:

sql = ''
for value_to_check in [1,2,6]:

sql = sql + """
Select
..case
....when
......value_to_check >= (select low_range1 from range_table) and value_to_check <= (select high_range1 from range_table)
....then
......value_to_check as value
......'Range1' as range
....when
......value_to_check >= low_range2 and value_to_check <= high_range2
....then
......value_to_check as value
......'Range2' as range
....when
......value_to_check >= low_range3 and value_to_check <= high_range3
....then
......value_to_check as value
......'Range3' as range
....else
......value_to_check as value
......'No Range Found' as range

**Unless end loop** 'Union All'
"""

Not sure if this is what you are looking for. If not I am willing to take another stab at it with you.

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 565 posted 11:14 pm on Jun 5, 2006 (gmt 0)

Not exactly...

We finally decided to just brute force it and loop through the list. We dumped the data from the table into a temp table (instead of hitting the database table each trip through the loop) and ran some tests. The performance wasn't as bad as we feared, so I guess we'll stick with the brute force method.

Thanks anyway!

Demaestro

WebmasterWorld Senior Member demaestro us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 565 posted 3:09 pm on Jun 6, 2006 (gmt 0)

No problem, sorry I didn't have more time yesterday to really get my head around it. If you ever go to optmize it I would be up for taking another stab at it.

syber

10+ Year Member



 
Msg#: 565 posted 1:31 pm on Jun 8, 2006 (gmt 0)

I think this will work for you:


SELECT num1, (SELECT range
FROM rangetable
WHERE numlist.num1 BETWEEN col1 AND col2)
FROM numlist
WHERE (SELECT range
FROM rangetable
WHERE numlist.num1 BETWEEN col1 AND col2)
IS NOT NULL

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved