DBISAM latest

How do I get a single max value from 4 other values in a record. This is
probably embarrassingly easy so forgive me and TIA

Date, V1, V2, V3 , V4

How do I determine which V is greatest?

There  has got be a simpler way but something crazy that should work is :

select IF( IF(v1>v2 then v1 else v2) > IF(v3>v4 then v3 else v4) then IF(v1>v2 then v1
else v2) else IF(v3>v4 then v3 else v4)) maxval from mytable

Raul

> How do I get a single max value from 4 other values in a record. This is
> probably embarrassingly easy so forgive me and TIA
> Date, V1, V2, V3 , V4
> How do I determine which V is greatest?

Sean


I assume you're talking SQL. If so you can use nested IFs. It gets a bit hairy with a lot of repetition  but its achievable. I'm sure someone else can do better


IF(
IF(V1>V2 THEN V1 ELSE V2) > V3,IF(V1>V2 THEN V1 ELSE V2),V3)
)

I'll let you type the rest

Roy Lambert

>
> IF(
> IF(V1>V2 THEN V1 ELSE V2) > V3,IF(V1>V2 THEN V1 ELSE V2),V3)
> )
>
> I'll let you type the rest
>

Or .. if you want something more readable ..

select V1
union
Select V2
union
....

Then you can do a MAX on the query result.

--

Malcolm


Very interesting if all you want is the biggest biggest but can you expand that to give it on a row by row basis with other data from the table?

Roy Lambert

Roy Lambert wrote:

> Malcolm
>
>
> Very interesting if all you want is the biggest biggest but can you
> expand that to give it on a row by row basis with other data from
> the table?
>
> Roy Lambert

Hi Roy

The OP was not specific about the use.  
Could have been a single record calculation .. all the way to a
global update.

I guess you would have to include one or more additional columns to
allow the subsequent MAX(V) to be qualified by Group and Having
clauses.  Depends what you want to do with the max value(s).

I had a requirement for something vaguely similar on a single record
(discard top one or two and bottom one or two values, then process
the remainder).  I selected the values (up to 11) with an Order By,
then just Query.First; .Delete; .Delete; .Last; .Delete; .Delete; ..
use what is left ..
Would probably have been much easier using looped TTable operations
throughout!

Malcolm
--

Malcolm


>The OP was not specific about the use.
>Could have been a single record calculation .. all the way to a
>global update.

I know. Just wanted to see if you could do it

>Would probably have been much easier using looped TTable operations
>throughout!

If it was going to be used much that's the sort of thing I'd go for a custom function.

Roy Lambert

If using triggers is an option then it might be even simpler to just create
an extra field in table and attach a trigger that calculates  the max value
before insert/update. Then sql and rest of operations become really trivial
then.

Raul


>>Would probably have been much easier using looped TTable operations
>>throughout!
>
> If it was going to be used much that's the sort of thing I'd go for a
> custom function.
>

Roy Lambert wrote:

> If it was going to be used much that's the sort of thing I'd go for
> a custom function.
>

Were they around in the good ol' dbisam days? <misty eyes>
... or did you just mean to code a delphi function ..

--

Malcolm


V4 definitely, I have a slew of them in my app. Written in Dephi but then so are some of those I have in the new shiny ElevateDB.

Roy Lambert