Query Help - 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 2005 Forums

Transact-SQL (2005)

Query Help

Author

Topic

Zook
Starting Member

5 Posts

Posted - 2008-05-01 : 18:19:41

I just took a new job. I inherited this query but think it could be better. I want to not only return the max value but also the max time for that value. The results at the bottom show that the max value isn't unique, so I get the max value for every hour it was maxed. I haven't had any luck creating a sub query on my own that gets the max time of the max value. Thanks in advance for any help.

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 : 02:44:54

May be this:-

SELECT *
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY  data_definitions.bfd_key
, stations.name
, data_definitions.name
, data_definitions.units_name
, data_values_3_2008.value ORDER BY  data_values_3_2008.time DESC) AS RowNo,
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)
)t
WHERE t.RowNo=1

Zook
Starting Member

5 Posts

Posted - 2008-05-02 : 09:22:59

Thank you. I appreciate you looking at this for me.

I tried the query and get the error bellow. It then became obvious to me that I'm running SQL 2000. Sorry I'm a complete noob. I'll take this topic down to the SQL 2000 forum.

Server: Msg 195, Level 15, State 10, Line 5
[Microsoft][ODBC SQL Server Driver][SQL Server]'ROW_NUMBER' is not a recognized function name.

Thanks, Corey

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-05-02 : 10:02:51

so... are you actually using SQL2000 rather than 2005?

Em

Zook
Starting Member

5 Posts

Posted - 2008-05-02 : 10:14:48

Correct. SQL2000 not 2005 as I was told. I took the time to remote into the server and look it up as I should have done before I posted.

My apologies, Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources