Select rows where column value has changed

by

I think this is what you’re after:

;WITH x AS
(
  SELECT value, time, rn = ROW_NUMBER() OVER 
  (PARTITION BY Value ORDER BY Time)
  FROM dbo.table
)
SELECT * FROM x WHERE rn = 1;

This may be slow if the resultset is large and there isn’t a good supporting index…

EDIT

Ah, wait a second, the values go up and down, not just up… if that is the case you can try this much slower approach:

DECLARE @x TABLE(value INT, [time] DATETIME)

INSERT @x VALUES
(0,'20120615 8:03:43 PM'),--
(1,'20120615 8:03:43 PM'),--*
(1,'20120615 8:03:48 PM'),--
(1,'20120615 8:03:53 PM'),--
(1,'20120615 8:03:58 PM'),--
(2,'20120615 8:04:03 PM'),--*
(2,'20120615 8:04:08 PM'),--
(3,'20120615 8:04:13 PM'),--*
(3,'20120615 8:04:18 PM'),--
(3,'20120615 8:04:23 PM'),--
(2,'20120615 8:04:28 PM'),--*
(2,'20120615 8:04:33 PM');

;WITH x AS
(
  SELECT *, rn = ROW_NUMBER() OVER (ORDER BY time)
  FROM @x
)
SELECT x.value, x.[time]
FROM x LEFT OUTER JOIN x AS y
ON x.rn = y.rn + 1
AND x.value <> y.value
WHERE y.value IS NOT NULL;

Results:

value  time
-----  -----------------------
1      2012-06-15 20:03:43.000
2      2012-06-15 20:04:03.000
3      2012-06-15 20:04:13.000
2      2012-06-15 20:04:28.000

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. T-SQL split string
  3. Can I Comma Delimit Multiple Rows Into One Column? [duplicate]
  4. How can I get column names from a table in SQL Server?
  5. Conditional WHERE clause in SQL Server
  6. How can I select the first day of a month in SQL?
  7. Is the NOLOCK (Sql Server hint) bad practice?
  8. Multi-statement Table Valued Function vs Inline Table Valued Function
  9. Remove trailing zeros from decimal in SQL Server
  10. Select statement to find duplicates on certain fields
  11. Where value in column containing comma delimited values
  12. Parse comma-separated string to make IN List of strings in the Where clause
  13. What is the difference between using a cross join and putting a comma between the two tables?
  14. Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)
  15. Compare DATETIME and DATE ignoring time portion
  16. How to create a PivotTable in Transact/SQL?
  17. Split function in SQL Server 2008
  18. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  19. Procedure or function !!! has too many arguments specified
  20. SQL “between” not inclusive
  21. How to efficiently delete rows while NOT using Truncate Table in a 500,000+ rows table
  22. T-SQL and the WHERE LIKE %Parameter% clause
  23. Can I set ignore_dup_key on for a primary key?
  24. Which SQL query is faster? Filter on Join criteria or Where clause?
  25. Generate all combinations in SQL
  26. How can I group by date time column without taking time into consideration
  27. When using GETDATE() in many places, is it better to use a variable?
  28. Average of multiple columns
  29. COALESCE Function in TSQL
  30. Execute sp_executeSql for select…into #table but Can’t Select out Temp Table Data

Leave a Comment