#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2004
    Posts
    6
    Rep 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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    128
    Rep Power
    71
    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
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2004
    Posts
    6
    Rep 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
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    128
    Rep Power
    71
    Originally Posted by GianniP
    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
    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
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2004
    Posts
    6
    Rep 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 ?
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2004
    Posts
    6
    Rep Power
    0
    changed it to (((OilQuantityPrice.OilQuantityVolume)<=800))

    will test some more but I think that should do it.. thanks very much
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    128
    Rep Power
    71
    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

IMN logo majestic logo threadwatch logo seochat tools logo