Limiting WHERE to first match

[groleau]

Each row in Accounts needs to be assigned a person from Counselors. The way the client prefers to do this is like

Fred gets Accounts where last name has starting letters from 'a' through 'fe'

We're trying to give them "better service" by letting them change the criteria, but of course we don't want them to have to write SQL.

I can see at least three ways to do it, except that I don't see the entire way.

Method one:

Counselor GE LT
--------- --- ----
Fred a ff
Sally ff ko
etc.

Here, my challenge is to automatically keep LT(i) = GE(i+1) rather than requiring the user to understand and apply the difference between exclusive and inclusive endpoints. (Also have to prevent them from getting out of order.)

Method two:


Counselor GE
--------- ---
Fred a
Sally ff
etc.

If the name is Adams or Felton, Fred will match >=
But if it's Fox, Fred and Sally will match. Here my challenge is to only take the first match for each row in Accounts.

The VB method would loop through Counselors, and return the first match. Not too hard, but it would be nice to keep it in the SQL.




--
Wes Groleau

http://www.Parkview.com/

 

[PHV]

A starting point (SQL code)
SELECT Counselor
FROM Counselors
WHERE GE = (SELECT Max(GE) FROM Counselors WHERE GE <= 'Fox')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[groleau]

In fact, it helped a LOT.

Took less than a minute to do the rest!

(change the literal to ref GE and add the other table)

THANKS!!

--
Wes Groleau

http://www.Parkview.com/