Need to filter out unchanged data 1

[patrussell]

I have a time based table that is written to every 5 seconds. I need to write a query that will only pull data from the table when one of 3 values has changed. A simple select query is below with a sample of the data.

SELECT     
	timestamp, 
	ACIDHFTOT_GALS_VAL0 AS HF, 
	ACIDNITRICTOT_GALS_VAL0 AS Nit, 
	ACIDSULFTOT_GALS_VAL0 AS Sulf
FROM dbo.ACIDUSAGE

[tt]
timestamp HF Nit Sulf
2011-01-07 08:51:57.487 17337 10530 3570
2011-01-07 08:51:52.487 17337 10530 3570
2011-01-07 08:51:47.487 17337 10535 3570
2011-01-07 08:51:42.487 17337 10536 3570
2011-01-07 08:51:37.487 17337 10537 3570
2011-01-07 08:51:32.487 17337 10537 3570
[/tt]

What I need returned from this data set is rows 1,3,4,5 since the actual data for each record has changed. With the timestamp changing every five seconds my SQL skills are not good enough to come up with a query that will accomplish this.

I hope I've provided enough information. Thanks for any help you can give.



Pat Russell

 

[gmmastros]

What version of SQL Server are you using? If you are using SQL2005 (or newer) you could write a relatively simple query to get this information.

Below is a sample to get you started. I took your sample data and loaded it in to a table variable so I could test the query. You can copy/paste the entire code to a query window and run it. If you are satisfied with the results, you can modify the query to use your actual table instead of the table variable.

Declare @Temp Table([timestamp] DateTime,HF int,Nit int,Sulf Int)

Insert Into @Temp Values('2011-01-07 08:51:57.487',    17337,    10530,    3570)
Insert Into @Temp Values('2011-01-07 08:51:52.487',    17337,    10530,    3570)
Insert Into @Temp Values('2011-01-07 08:51:47.487',    17337,    10535,    3570)
Insert Into @Temp Values('2011-01-07 08:51:42.487',    17337,    10536,    3570)
Insert Into @Temp Values('2011-01-07 08:51:37.487',    17337,    10537,    3570)
Insert Into @Temp Values('2011-01-07 08:51:32.487',    17337,    10537,    3570)

;with NumberedResults
As
(
  Select Row_Number() Over (Partition By HF, Nit, Sulf Order By [timestamp]) As RowId,
         [timestamp],
         HF, Nit, Sulf
  From   @Temp
)
Select [timestamp], HF, Nit, Sulf
From   NumberedResults
Where  RowId = 1
Order By [timestamp]

If you are using SQL2000 (or less), let me know.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[patrussell]

The original table is in SQL2000 but I do have an SQL2008 server that I can move the data to.

I tested the query in the 2008 server and it worked great.

I think I need to learn more about the newer query options/commands.

Thanks for the help!


Pat Russell

 

[patrussell]

Worked beautifully!

Thanks!


Pat Russell

 

[gmmastros]

You're welcome.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom