Best solution for this query

Dev Shed Forums Sponsor:

October 5th, 2011, 04:32 AM

GianniP

Registered User

Join Date: Nov 2004

Posts: 6

Time spent in forums: 1 h 49 m 20 sec
Reputation Power: 0

Best solution for this query

Hi

I would like to write a query to return a number associated to the closest match of another.

I'll try and explain properly.

I have 4 rows: 250,450,650,850

Each of those has a value attached e.g. 1,4,6,9

I would like to return 1 if someone chosses a number between 250 and 450, 4 if 450 to 650 etc

Appreciate any help

October 5th, 2011, 05:33 AM

recyan

Contributing User

Join Date: Nov 2010

Posts: 116

Time spent in forums: 2 Days 10 h 47 m 4 sec
Reputation Power: 70

Hi,

just see if below gives some guidelines :

Code:

SELECT top 1 
	mytable.testNumber
	,mytable.testAssociate
FROM 
	mytable
WHERE 
	(((mytable.testNumber)<800))
ORDER BY 
	mytable.testNumber DESC;

Thanks

Comments on this post

GianniP agrees!

October 5th, 2011, 07:45 AM

GianniP

Registered User

Join Date: Nov 2004

Posts: 6

Time spent in forums: 1 h 49 m 20 sec
Reputation Power: 0

I think I'm not explaining it correctly.

The table looks like this

-------------------------
id | volume | amount |
-------------------------
1 250 2
2 450 3.5
3 650 4
4 850 6
-------------------------

The web form allows for the input of any number form 250. If the person enters 346 for example, the query needs to return 2. If it's 451 it needs to return 3.5 etc

October 5th, 2011, 08:00 AM

recyan

Contributing User

Join Date: Nov 2010

Posts: 116

Time spent in forums: 2 Days 10 h 47 m 4 sec
Reputation Power: 70

Quote:

Originally Posted by GianniP

What happened when u tried the earlier posted query, something like below ?

Code:

SELECT TOP 1 
mytable.[volume]
,mytable.[amount]
FROM 
mytable
WHERE 
(((mytable.[volume])<800))
ORDER BY 
mytable.[volume] DESC;

The

800

is the form input value which is passed to the query

Thanks

October 5th, 2011, 08:20 AM

GianniP

Registered User

Join Date: Nov 2004

Posts: 6

Time spent in forums: 1 h 49 m 20 sec
Reputation Power: 0

:-) Sorry..

It almost works but if I put in 650 it then would select 450 rather than 650 which IS present in the db. So I assume it needs to check if there is such a volume and if not, go with the lower volume.

Make sense ?

October 5th, 2011, 08:22 AM

GianniP

Registered User

Join Date: Nov 2004

Posts: 6

Time spent in forums: 1 h 49 m 20 sec
Reputation Power: 0

changed it to (((OilQuantityPrice.OilQuantityVolume)<=800))

will test some more but I think that should do it.. thanks very much

October 6th, 2011, 10:58 PM

recyan

Contributing User

Join Date: Nov 2010

Posts: 116

Time spent in forums: 2 Days 10 h 47 m 4 sec
Reputation Power: 70

Quote:

Originally Posted by GianniP

changed it to (((OilQuantityPrice.OilQuantityVolume)<=800))

will test some more but I think that should do it.. thanks very much

Glad you found it helpful.
Noticed title of your post "Best solution for this query".
I really donot know whether what I posted is the best solution or not.

Thanks

Best solution for this query

Developer Shed Advertisers and Affiliates