max date of the max value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

max date of the max value

Author

Topic

Zook
Starting Member

5 Posts

Posted - 2008-05-02 : 09:55:36

Hi all, I'm a noob that originally posted this in the wrong forum. The following query lists the max value for the key that I want to query. It works great. However, my problem is that if the max value is not unique, it returns an entry for every hour and day that the max value was the same. What I'd like is a sub query that just returns the max time of the max value. With the current query I have to manualy delete the extra entries. Any help is appreciated; I'm in way over my head.

Thanks, Corey

Query:

SELECT
data_definitions.bfd_key
, stations.name
, data_definitions.name
, data_definitions.units_name
, data_values_3_2008.time
, data_values_3_2008.value
FROM hist.dbo.data_definitions data_definitions
, hist.dbo.data_values_3_2008 data_values_3_2008
, hist.dbo.stations stations
WHERE data_definitions.bfd_key = data_values_3_2008.bfd_key AND
data_definitions.station_id = stations.station_id AND
(data_values_3_2008.bfd_key = '03303001') AND
(data_values_3_2008.time Between {ts '2008-03-01 01:00:00'} And
{ts '2008-03-31 00:00:00'}) AND
(data_values_3_2008.data_type=5)
GROUP BY data_definitions.bfd_key
, stations.name
, data_definitions.name
, data_definitions.units_name
, data_values_3_2008.time
, data_values_3_2008.value
HAVING MAX(ABS(data_values_3_2008.value))=(SELECT MAX(ABS(data_values_3_2008.value))
FROM hist.dbo.data_values_3_2008 data_values_3_2008
WHERE
(data_values_3_2008.bfd_key = '03303001') AND
(data_values_3_2008.time Between
{ts '2008-03-01 01:00:00'} And
{ts '2008-03-31 00:00:00'}) AND (data_values_3_2008.data_type=5))

Results:

bfd_key,name,name,units_name,time,value
03303001,MORGAN,INTERUPT MW,MW,2008-03-01 01:00:00.000,.5626
03303001,MORGAN,INTERUPT MW,MW,2008-03-01 02:00:00.000,.5626
03303001,MORGAN,INTERUPT MW,MW,2008-03-01 03:00:00.000,.5626
03303001,MORGAN,INTERUPT MW,MW,2008-03-01 04:00:00.000,.5626
03303001,MORGAN,INTERUPT MW,MW,2008-03-01 05:00:00.000,.5626
03303001,MORGAN,INTERUPT MW,MW,2008-03-01 06:00:00.000,.5626
03303001,MORGAN,INTERUPT MW,MW,2008-03-01 07:00:00.000,.5626

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-02 : 13:47:16

Try this:-

SELECT
data_definitions.bfd_key
, stations.name
, data_definitions.name
, data_definitions.units_name
, MAX(data_values_3_2008.time)
, data_values_3_2008.value
FROM hist.dbo.data_definitions data_definitions
, hist.dbo.data_values_3_2008 data_values_3_2008
, hist.dbo.stations stations
WHERE data_definitions.bfd_key = data_values_3_2008.bfd_key AND 
data_definitions.station_id = stations.station_id AND
(data_values_3_2008.bfd_key = '03303001') AND 
(data_values_3_2008.time Between {ts '2008-03-01 01:00:00'} And 
{ts '2008-03-31 00:00:00'}) AND 
(data_values_3_2008.data_type=5)
GROUP BY data_definitions.bfd_key
, stations.name
, data_definitions.name
, data_definitions.units_name
, data_values_3_2008.value
HAVING MAX(ABS(data_values_3_2008.value))=(SELECT MAX(ABS(data_values_3_2008.value))
FROM hist.dbo.data_values_3_2008 data_values_3_2008
WHERE 
(data_values_3_2008.bfd_key = '03303001') AND 
(data_values_3_2008.time Between 
{ts '2008-03-01 01:00:00'} And 
{ts '2008-03-31 00:00:00'}) AND (data_values_3_2008.data_type=5))

Zook
Starting Member

5 Posts

Posted - 2008-05-02 : 13:54:53

That worked perfect. Thank you!! You have no idea how much time you've saved me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources