finding absolute maximum value 2

[bj1]

Hi

This is the original data:

37 2003-12-13 00:56:43.000 -199.0
37 2003-12-13 00:56:43.000 -156.0
38 2003-12-13 00:56:43.000 -9.0
38 2003-12-13 00:56:43.000 3.0
37 2003-12-13 00:56:44.000 117.0

What i would like to see some how is:

37 2003-12-13 00:56:43.000 -199.0
38 2003-12-13 00:56:43.000 -9.0
37 2003-12-13 00:56:44.000 117.0

i.e the maximum absolute value.

I have written this so far:

select xdsensorid, xddatetime, max(abs(xdvalue)), xdlongitude, xdlatitude
from ((vmi_vehicles
inner join ext_sensors on vunitid = sunitid)
inner join his_externaldata on xdsensorid = ssensorid)
where vdescription = 'Shell 153456'
and (schannelnumber = 54 or schannelnumber = 55)
group by xdSensorID, xdDateTime, xdlongitude, xdlatitude
order by xddatetime, xdsensorid

and get this result:

37 2003-12-13 00:56:43.000 199.0
38 2003-12-13 00:56:43.000 9.0
37 2003-12-13 00:56:44.000 117.0

is there any way to write this query and if the value happens to be a negative to keep the negative sign.

Thanks for your help,

B

 

[SantaMufasa]

BJ1,

Yes, I would do your original query with modifications that look something like this:

select ..., xdvalue, ...
from ...
where ...
and abs(xdvalue) = (select max(abs(xdvalue))
from ...
where ...)
and ...
order by ...

Is this enough code for you to get the idea?

Mufasa
(aka Dave of Sandy, Utah, USA @ 10:01 (05Jan04) GMT, 03:01 (05Jan04) Mountain Time)

 

[bj1]

hi mufasa,
i tried your suggestion and came up with this query:

select xdsensorid, xddatetime, xdvalue, xdlongitude, xdlatitude
from his_externaldata
where abs(xdvalue) =

(select max(abs(xdvalue))
from ((vmi_vehicles
inner join ext_sensors on vunitid = sunitid)
inner join his_externaldata on xdsensorid = ssensorid)
where vdescription = 'Shell 153456'
and (schannelnumber = 54 or schannelnumber = 55))

group by xdSensorID, xdDateTime, xdvalue, xdlongitude, xdlatitude
order by xddatetime, xdsensorid

this gave me only one row...

37 2003-12-13 00:56:43.000 -199.0

does this mean it is only working out the the one maximum entry???

Thanks,
B

 

[carp]

Currently, it appears there is only one row that matches the results of your subquery. This is because your subquery is pulling the maximum absolute value for the entire data set, whereas I believe you want the max absolute value for each sensor.

I think this might get you over that hump:

select xdsensorid, xddatetime, xdvalue, xdlongitude, xdlatitude
from his_externaldata o
where abs(xdvalue) =
(select max(abs(xdvalue))
from ((vmi_vehicles
inner join ext_sensors on vunitid = sunitid)
inner join his_externaldata on xdsensorid = ssensorid)
where vdescription = 'Shell 153456'
and (schannelnumber = 54 or schannelnumber = 55)
AND xdsensorid = o.xdsensorid)

group by xdSensorID, xdDateTime, xdvalue, xdlongitude, xdlatitude
order by xddatetime, xdsensorid;

Elbert, CO
1613 MST

 

[bj1]

i would like to find the max absolute value for each distinct sensorid and datetime. And then i want to be able to show these values in their original form, i.e whether they are positive or negative.

there will be many entries for sensor id 54 and 55, but at different times throughout the day. so i want to record the abs max for the different times.

Does this make sense?

thanks for your help.

 

[SantaMufasa]

BJ1,

Then building on the code that Carp posted earlier, just add in the constraint of time:

select xdsensorid, xddatetime, xdvalue, xdlongitude, xdlatitude
from his_externaldata o
where abs(xdvalue) =
    (select max(abs(xdvalue))
     from ((vmi_vehicles
     inner join ext_sensors on vunitid = sunitid) 
     inner join his_externaldata on xdsensorid = ssensorid)
     where vdescription = 'Shell 153456' 
     and (schannelnumber = 54 or schannelnumber = 55)
     AND xdsensorid = o.xdsensorid
     AND xddatetime = o.xddatetime)
/* I although I haven't executed the code, I don't believe
   that the following "GROUP BY" code is necessary since
   the last two "ANDs" (above) should be limiting output
   to a single row anyway
group by xdSensorID, xdDateTime, xdvalue, xdlongitude, xdlatitude
*/
order by xddatetime, xdsensorid;

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA @ 23:49 (05Jan04) GMT, 16:49 (05Jan04) Mountain Time)

 

[bj1]

This works!!!
Thank you so much for your help...

Keep up the good work.

Thanks heaps,
B

 

[bj1]

sorry... i have another question!

How would i go about converting this:

37 2003-12-13 00:56:43.000 -199.0
38 2003-12-13 00:56:43.000 -9.0
37 2003-12-13 00:56:44.000 117.0

to this:

xddatetime tilt54 pitch55

2003-12-13 00:56:43.000 -199.0 -9.0
2003-12-13 00:56:44.000 117.0

where tilt54 is sensorid 37 and pitch55 is sensorid 38.

is this possible?

thanks

 

[SantaMufasa]

BJ1,

Here are the test data and the code to do as you wish. The reason for the UNION is to outer join values to ensure printing rows for which there is a value for either "tilt54" or "pitch55", but not both.

The test bed:

create table sensorreadings
	(xdsensorid	number
	,xddatetime	timestamp
	,xdvalue	number);
insert into sensorreadings values
	(37,to_timestamp('2003-12-13 00:56:43.000','yyyy-mm-dd hh24:mi:ss.ff3'),-199.0);
insert into sensorreadings values
	(38,to_timestamp('2003-12-13 00:56:43.000','yyyy-mm-dd hh24:mi:ss.ff3'),-9.0);
insert into sensorreadings values
	(37,to_timestamp('2003-12-13 00:56:44.000','yyyy-mm-dd hh24:mi:ss.ff3'),117.0);
commit;

Your SELECT:

col a heading "xddatetime" format a29
select to_char(a.xddatetime) a, tilt54, pitch55
from	(select xdsensorid, xddatetime, xdvalue tilt54 from sensorreadings
		where xdsensorid = 37) a,
	(select xdsensorid, xddatetime, xdvalue pitch55 from sensorreadings
		where xdsensorid = 38) b
where a.xddatetime = b.xddatetime(+)
union
select to_char(a.xddatetime) a, tilt54, pitch55
from	(select xdsensorid, xddatetime, xdvalue tilt54 from sensorreadings
		where xdsensorid = 37) a,
	(select xdsensorid, xddatetime, xdvalue pitch55 from sensorreadings
		where xdsensorid = 38) b
where a.xddatetime(+) = b.xddatetime
order by a
/
[code]

Let me know if this is what you wanted.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA @ 05:37 (06Jan04) GMT, 22:37 (05Jan04) Mountain Time)

 

[SantaMufasa]

...and the results (sorry):

xddatetime                        TILT54    PITCH55
----------------------------- ---------- ----------
13-DEC-03 12.56.43.000000 AM        -199         -9
13-DEC-03 12.56.44.000000 AM         117

Mufasa
(aka Dave of Sandy, Utah, USA @ 05:38 (06Jan04) GMT, 22:38 (05Jan04) Mountain Time)

 

[bj1]

Thanks Mufasa!

This looks great... but... now i lose finding the max absolute values for each date.

Is it possible to work this aspect into the query as well?

Thanks so much for your help.

 

[SantaMufasa]

BJ,

Since Carp and I have assisted you with working COMPONENTS of your solution, Carp and I propose that you make a stab at assembling the different pieces, wrestling with any syntactical issues to the best of your experience, then post either your successful results, or otherwise post your code with error messages, then we can go from there.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:56 (06Jan04) GMT, 12:56 (06Jan04) Mountain Time)

 

[bj1]

Sounds fair enough to me...
Thank you for all your help. I'll let you know how i go.
BJ

 

[sem]

I may suggest another solution (based on Dave's example):

select xddatetime
, decode(sign(mx37 + mn37), -1, mn37, mx37) tilt54
, decode(sign(mx38 + mn38), -1, mx38, mn38) pitch55
from (
select xddatetime
, max(decode(xdsensorid, 37, null, xdvalue )) mx38
, min(decode(xdsensorid, 37, null, xdvalue )) mn38
, max(decode(xdsensorid, 38, null, xdvalue )) mx37
, min(decode(xdsensorid, 38, null, xdvalue )) mn37
from sensorreadings
where xdsensorid in (37, 38)
group by xddatetime)

The idea is that ABS has its extremums on either MIN or MAX. Thus I select both and then calculate their sum: if the result is positive, then ABS(MIN)<ABS(MAX), and MAX should be chosen. Similarly if the result is negative, then ABS(MIN)>ABS(MAX), and MIN should be chosen.
If my logic has no errors (had a party yestarday ), this query should be more efficient as it accesses sensorreadings only once.

Regards, Dima

http://seminihin.narod.ru