EasyCFM.COM ColdFusion Forums / MySQL Related Issues / next highest value in list

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: next highest value in list

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

Lossed
11-13-2007 @ 4:33 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1095
Joined: Apr 2004

Say I have an ascending list:
  (1,2,3,5,6,10,15,30,45,60,90,180)
and I have a search value:
  37
and if the search value doesn't match any from the list, I need to return the next on the list, in this case, 45.

Is there any function that will do that?
If not, can someone please help with the syntax?

It is a nested select buried deep within the mother of all queries and I'm just not seeing the wood for the trees anymore Frown .

Lossed
---------------------------
When the only tool you have is a hammer, everything looks like a nail Smile
-----------------------------

This message was edited by Lossed on 11-13-07 @ 6:42 PM

Lossed
11-13-2007 @ 6:42 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1095
Joined: Apr 2004

I was looking at interval() but it still means I have to do a listGetAt() in CF, when I'd rather the query return the actual value.





Lossed
---------------------------
When the only tool you have is a hammer, everything looks like a nail Smile
-----------------------------

Webmaster
11-14-2007 @ 11:59 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4542
Joined: Jan 2002

You can loop through the list and insert into a temp query... then do a QoQ to return TOP 1 where value ? 37

That is probably your best bet.

Pablo Varando
Senior Application Architect
EasyCFM.COM, LLC.

904.483.1457 \\ mobile
webmaster@easycfm.com \\email

Lossed
11-14-2007 @ 2:16 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1095
Joined: Apr 2004

Cheers Pablo.
I've been trying to take as much load off CF as possible and contain it within the SQL.
Interval() wasn't working out as originally hoped, so there is always [case when then end] in the SQL but that (or a bunch of messy nested if()'s) is about the only SQL way I've come up with that works and ain't pretty:


case
     when theValue in (1,2,3,5,6,10,15,30,45,60,90,180) then theValue
     when theValue = 4 then 5
     when theValue <10 then 10
     when theValue <15 then 15
     when theValue <30 then 30
     when theValue <45 then 45
     when theValue <60 then 60
     when theValue <90 then 90
     else 180
end

**edit** I would usually order the whens the other way around, but because the values are more likely to be low, I've ordered it to avoid as many whens as possible.

Lossed
---------------------------
When the only tool you have is a hammer, everything looks like a nail Smile
-----------------------------

This message was edited by Lossed on 11-14-07 @ 2:20 PM


Website Designed and Developed by Pablo Varando.