Query for Min, Max and times they occured?

Ignition
1

I need to query a monthly history table to pull the minimum and maximum values and the times they occured for a report. I know to use min() and max() functions to get the values but not sure how to get the times?

thanks for any help,
Scott

2 
SELECT time_stamp FROM table WHERE value=(select MAX(value) from table) ;
3

Thanks for the help but i’m not sure this works for me. here is a sample of the query I’m using to populate a report. The report gives the min, max, and avg value for each day over a period of x days for given tags. I need to work the times for the min and max into the query. Ideally i would like to get two more columns for MinTime and MaxTime.

select date(FROM_UNIXTIME(t_stamp/1000)) as ‘Date’,
max(FloatValue) as Max,
min(FloatValue) as Min,
avg(FloatValue) as Avg,
tagpath as Tag
from sqlt_data_1_2013_08 as data, sqlth_te as tag
where data.tagid = tag.id and tag.tagpath = ‘Effluent/AWN_Eff_flow’ and
{Root Container.WhereClause} // This is the date range of the report
group by Date
Union
{repeat for additional tags}

Again, Thanks for the help.

4

Here's an example I was given (from an SQL help forum) which does what you require.

DECLARE @t TABLE 
(	
[ndx] [int] IDENTITY(1,1) NOT NULL,	
[value] [int] NULL,	[t_stamp] [datetime] NULL);
INSERT INTO @t VALUES (1,'20130823')
INSERT INTO @t VALUES (1,'20130824')
INSERT INTO @t VALUES (2,'20130825')
INSERT INTO @t VALUES (3,'20130826')
INSERT INTO @t VALUES (4,'20130827'
)INSERT INTO @t VALUES (5,'20130828')
INSERT INTO @t VALUES (5,'20130829')
;WITH 
q1 (min_value, min_t_stamp) AS (
	SELECT min(value), min(t_stamp) 
	FROM @t
	WHERE value = (SELECT min(value) FROM @t)
),
q2 (min_value, min_t_stamp) AS (
	SELECT min(value), min(t_stamp) 
	FROM @t
	WHERE value = (SELECT max(value) FROM @t)
)
SELECT * 
FROM q1, q2

Or this:

WITH a (aval, astamp) AS (
	SELECT min(value), min(t_stamp) from @t
	WHERE value = (SELECT min(value) FROM @t)
	)
, b (bval, bstamp) AS (
	SELECT min(value), min(t_stamp) from @t
	WHERE value = (SELECT max(value) FROM @t)
	)
SELECT
	a.aval
,	a.astamp
,	b.bval
,	b.bstamp
FROM
	a
CROSS JOIN b

And this does the same:

SELECT 
	col1 = MIN(CASE WHEN t.value = x.min_value THEN value ELSE NULL END), 
	col2 = MIN(CASE WHEN t.value = x.min_value THEN t_stamp ELSE NULL END), 
	col3 = MIN(CASE WHEN t.value = x.max_value THEN value ELSE NULL END), 
	col4 = MIN(CASE WHEN t.value = x.max_value THEN t_stamp ELSE NULL END) 
FROM @t t
CROSS APPLY (
	SELECT min_value = MIN(value), max_value = MAX(value) 
	FROM @t
) x
WHERE t.value IN (x.min_value, x.max_value)