# Query for Min, Max and times they occured?

**URL:** <https://forum.inductiveautomation.com/t/query-for-min-max-and-times-they-occured/7148>\
**Category:** Ignition\
**Created:** [August 27, 2013, 12:41am UTC](https://forum.inductiveautomation.com/t/query-for-min-max-and-times-they-occured/7148 "2013-08-27T00:41:15Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![scott\_rister](https://avatars.discourse-cdn.com/v4/letter/s/5daacb/32.png) [@scott\_rister](https://forum.inductiveautomation.com/u/scott_rister)\
**Post date:** [August 27, 2013, 12:41am UTC](https://forum.inductiveautomation.com/t/query-for-min-max-and-times-they-occured/7148/1 "2013-08-27T00:41:15Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Micky.Franks](https://avatars.discourse-cdn.com/v4/letter/m/c57346/32.png) [@Micky.Franks](https://forum.inductiveautomation.com/u/Micky.Franks)\
**Post date:** [August 27, 2013, 2:14am UTC](https://forum.inductiveautomation.com/t/query-for-min-max-and-times-they-occured/7148/2 "2013-08-27T02:14:37Z")

</div>

```auto
SELECT time_stamp FROM table WHERE value=(select MAX(value) from table) ;
```

---

<div class="post-metadata">

**Author:** ![scott\_rister](https://avatars.discourse-cdn.com/v4/letter/s/5daacb/32.png) [@scott\_rister](https://forum.inductiveautomation.com/u/scott_rister)\
**Post date:** [August 27, 2013, 5:31pm UTC](https://forum.inductiveautomation.com/t/query-for-min-max-and-times-they-occured/7148/3 "2013-08-27T17:31:45Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Brett\_Yeomans](https://avatars.discourse-cdn.com/v4/letter/b/35a633/32.png) [@Brett\_Yeomans](https://forum.inductiveautomation.com/u/Brett_Yeomans)\
**Post date:** [September 1, 2013, 7:00am UTC](https://forum.inductiveautomation.com/t/query-for-min-max-and-times-they-occured/7148/4 "2013-09-01T07:00:13Z")

</div>

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

```auto
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:

```auto
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:

```auto
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)
```