Anbei, falls Interesse, noch ein Posting aus einer anderen Newsgroup mit einer alternativen L�sung (wenn auch �hnlichem Ansatz):
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
When I first started whitling on this problem, I too started with the dreaded "<>" sign. I say dreaded because "<>", NOT IN, and NOT EXISTS are famous for causing a recompilation of stats and also cripple the query optimizer. Translation? They all kill speed. My original code, depending on whether the "<>" was part of the JOIN or part of the WHERE was causing a 13 to 25 milli second recomp of the stats and then taking several milli seconds to run... and on just 9 records? Not satisfactory... So, I juggled the JOIN and whacked the WHERE so that I was using only positive logic ("=" and "IS") and got the darned thing to (finally, whew!) run so fast with 9 records that the stat counter can't even register the time.
Two side benefits of the way I eventually did it... I didn't need to add an extra SELECT to get the first record AND a very minor change in the code in two places (see embedded comments) will allow you to find the LAST item just before each change occurs!
I was particullary interested in this "problem post" because I have a project coming up in about a month where I'll need to reduce millions of rows of thermal data to just when the temperature changes and the date/time when it changed. This code will fit that bill perfectly!
Using the original data posted, here's the results I got...
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. ID Field1 Field2 ----------- ------ ------ 1920 ID16 Value1 1925 ID10 Value2 1929 ID20 Value3 1933 ID16 Value1
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
and here's the code...
------------------------------------------------------------------------------------------
--This example demonstrates how to find when the value in a particular column changes
--when compared to the previous row. FIELD2 is the target column for this example.
--See embedded comments for how to find when the value is ABOUT to change.
--First, if the test table exists, drop it.
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.ValChange')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table dbo.ValChange
GO
--Create the test table.
CREATE TABLE dbo.ValChange
(
ID INT IDENTITY NOT NULL,
Field1 VARCHAR(4) NOT NULL,
Field2 VARCHAR(6) NOT NULL
)
GO
--Allow inserts into IDENTITY column and insert data. Reset when done.
SET IDENTITY_INSERT dbo.ValChange ON
INSERT INTO ValChange (ID,Field1,Field2) VALUES (1920,'ID16','Value1')
INSERT INTO ValChange (ID,Field1,Field2) VALUES (1922,'ID16','Value1')
INSERT INTO ValChange (ID,Field1,Field2) VALUES (1923,'ID19','Value1')
INSERT INTO ValChange (ID,Field1,Field2) VALUES (1925,'ID10','Value2')
INSERT INTO ValChange (ID,Field1,Field2) VALUES (1926,'ID16','Value2')
INSERT INTO ValChange (ID,Field1,Field2) VALUES (1929,'ID20','Value3')
INSERT INTO ValChange (ID,Field1,Field2) VALUES (1930,'ID16','Value3')
INSERT INTO ValChange (ID,Field1,Field2) VALUES (1933,'ID16','Value1')
INSERT INTO ValChange (ID,Field1,Field2) VALUES (1934,'ID30','Value1')
SET IDENTITY_INSERT dbo.ValChange OFF
--Turn the stat timers on SET STATISTICS TIME ON
--Execute the process using a correlated sub-query to find the
--MAX ID that is less than the ID of each current row.
--In other words, it finds the previous ID
--It's called "correlated" because it makes a reference outside the ()
--Results will be each record whose Field2 (3rd field visible) has changed from the previous record.
--Can be reversed... see embedded comments in sub-query.
SELECT vc1.*
FROM ValChange vc1
LEFT OUTER JOIN
ValChange vc2
ON vc2.ID = (SELECT MAX(ID) AS ID --Change to MIN to find last value before the change
FROM ValChange
--This WHERE finds the previous row (and makes this a "correlated" subquery)
WHERE ID < vc1.ID) --Change to > to find last value before the change
AND vc1.FIELD2=vc2.FIELD2
--This WHERE makes sure the rows are different for the target column
WHERE vc2.FIELD2 IS NULL
ORDER BY vc1.ID
--Turn the stat timers off SET STATISTICS TIME OFF ------------------------------------------------------------------------------------------
--Jeff Moden
Claudius Ceteras wrote:
Check das mal:Das w�rde nur gehen wenn du die bisher als g�ltig gefundenen werte in eine temp tabelle umsortierst und eine weitere id mitlaufen l��t um den letztene zu identifizieren. wenn du dir n�mlich die rohdaten der frage anschaust gibt es dort keine eindeutige ID f�r die top abfrage.
Select * from tab t1 where t1.wert<>
(select top 1 t2.wert from tab t2 where t2.id<t1.id order by t2.id desc)
Also bei mir geht obige abfrage mit den geposteten testdaten, bloss das die erste zeile nicht kommt, weil die keinen vorg�nger hat, aber wenn ich die abfrage ein wenig ver�ndere geht das auch:
Select * from tab t1 where t1.wert<> Isnull((select top 1 t2.wert from tab t2 where t2.id<t1.id order by t2.id desc),'dummy')
Claudius
_______________________________________________ Coffeehouse Mailingliste, Postings senden an: [EMAIL PROTECTED] An-/Abmeldung und Suchfunktion unter: http://www.glengamoi.com/mailman/listinfo/coffeehouse
_______________________________________________ Coffeehouse Mailingliste, Postings senden an: [EMAIL PROTECTED] An-/Abmeldung und Suchfunktion unter: http://www.glengamoi.com/mailman/listinfo/coffeehouse
