26

I have a table that has an ID, a value, and a date. There are many IDs, Values, and dates in this table.

Records are inserted into this table periodically. The ID will always stay the same but occasionally the value will change.

How can I write a query that will give me the ID plus the most recent time the value has changed? Note: the value will always increase.

From this sample data:

  Create Table Taco
 (  Taco_ID int,
    Taco_value int,
    Taco_date datetime)

Insert INTO Taco 
Values (1, 1, '2012-07-01 00:00:01'),
        (1, 1, '2012-07-01 00:00:02'),
        (1, 1, '2012-07-01 00:00:03'),
        (1, 1, '2012-07-01 00:00:04'),
        (1, 2, '2012-07-01 00:00:05'),
        (1, 2, '2012-07-01 00:00:06'),
        (1, 2, '2012-07-01 00:00:07'),
        (1, 2, '2012-07-01 00:00:08')

The result should be:

Taco_ID      Taco_date
1            2012-07-01 00:00:05

(Because 00:05 was the last time Taco_Value changed.)

  • 2
    I assume taco has nothing to do with the food? – Kermit Jun 18 '13 at 19:19
  • 5
    I'm hungry and would like to eat some tacos. Just needed a name for the sample table. – SqlSandwiches Jun 18 '13 at 19:20
  • 8
    Did you pick your username on a similar basis? – Martin Smith Jun 18 '13 at 19:21
  • 1
    Quite Possible. – SqlSandwiches Jun 18 '13 at 19:43
13

These two queries rely on the assumption that Taco_value always increases over time.

;WITH x AS
(
  SELECT Taco_ID, Taco_date,
    dr = ROW_NUMBER() OVER (PARTITION BY Taco_ID, Taco_Value ORDER BY Taco_date),
    qr = ROW_NUMBER() OVER (PARTITION BY Taco_ID ORDER BY Taco_date)
  FROM dbo.Taco
), y AS
(
  SELECT Taco_ID, Taco_date,
    rn = ROW_NUMBER() OVER (PARTITION BY Taco_ID, dr ORDER BY qr DESC)
  FROM x WHERE dr = 1
)
SELECT Taco_ID, Taco_date
FROM y 
WHERE rn = 1;

An alternative with fewer window function madness:

;WITH x AS
(
  SELECT Taco_ID, Taco_value, Taco_date = MIN(Taco_date)
  FROM dbo.Taco
  GROUP BY Taco_ID, Taco_value
), y AS
(
  SELECT Taco_ID, Taco_date, 
    rn = ROW_NUMBER() OVER (PARTITION BY Taco_ID ORDER BY Taco_date DESC)
  FROM x
)
SELECT Taco_ID, Taco_date FROM y WHERE rn = 1;

Examples at SQLfiddle


Update

For those keeping track, there was contention over what happens if Taco_value could ever repeat. If it could go from 1 to 2 and then back to 1 for any given Taco_ID, the queries will not work. Here is a solution for that case, even if it isn't quite the gaps & islands technique that someone like Itzik Ben-Gan may be able to dream up, and even if it isn't relevant for the OP's scenario - it may be relevant to a future reader. It's a little more complex, and I also added an additional variable - a Taco_ID that only ever has one Taco_value.

If you want to include the first row for any ID where value didn't change at all in the entire set:

;WITH x AS
(
  SELECT *, rn = ROW_NUMBER() OVER 
    (PARTITION BY Taco_ID ORDER BY Taco_date DESC)
  FROM dbo.Taco
), rest AS (SELECT * FROM x WHERE rn > 1)
SELECT  
  main.Taco_ID, 
  Taco_date = MIN(CASE 
    WHEN main.Taco_value = rest.Taco_value 
    THEN rest.Taco_date ELSE main.Taco_date 
  END)
FROM x AS main LEFT OUTER JOIN rest
ON main.Taco_ID = rest.Taco_ID AND rest.rn > 1
WHERE main.rn = 1
AND NOT EXISTS 
(
  SELECT 1 FROM rest AS rest2
   WHERE Taco_ID = rest.Taco_ID
   AND rn < rest.rn
   AND Taco_value <> rest.Taco_value
) 
GROUP BY main.Taco_ID;

If you want to exclude those rows, it's a bit more complex, but still minor changes:

;WITH x AS
(
  SELECT *, rn = ROW_NUMBER() OVER 
    (PARTITION BY Taco_ID ORDER BY Taco_date DESC)
  FROM dbo.Taco
), rest AS (SELECT * FROM x WHERE rn > 1)
SELECT 
  main.Taco_ID, 
  Taco_date = MIN(
  CASE 
    WHEN main.Taco_value = rest.Taco_value 
    THEN rest.Taco_date ELSE main.Taco_date 
  END)
FROM x AS main INNER JOIN rest -- ***** change this to INNER JOIN *****
ON main.Taco_ID = rest.Taco_ID AND rest.rn > 1
WHERE main.rn = 1
AND NOT EXISTS
(
  SELECT 1 FROM rest AS rest2
   WHERE Taco_ID = rest.Taco_ID
   AND rn < rest.rn
   AND Taco_value <> rest.Taco_value
)
AND EXISTS -- ***** add this EXISTS clause ***** 
(
  SELECT 1 FROM rest AS rest2
   WHERE Taco_ID = rest.Taco_ID
   AND Taco_value <> rest.Taco_value
)
GROUP BY main.Taco_ID;

Updated SQLfiddle examples

|improve this answer|||||
  • I've noticed some significant performance issues with OVER but I've only used it a few times and may be writing it poorly. Have you noticed anything? – Kenneth Fisher Jun 18 '13 at 19:21
  • 1
    @KennethFisher not specifically with OVER. Like anything else, query constructs depends heavily on the underlying schema / indexes to work correctly. An over clause that partitions will suffer the same problems as a GROUP BY. – Aaron Bertrand Jun 18 '13 at 19:26
  • @KennethFisher please be careful to not draw broad, sweeping conclusions from singular, isolated observations. I see the same arguments against CTEs - "Well, I had this recursive CTE once, and its performance sucked. So I don't use CTEs anymore." – Aaron Bertrand Jun 18 '13 at 19:32
  • That's why I asked. I haven't used it enough to say one way or another, but the few times I've used it I was able to get better performance with a CTE. I'll keep playing with it though. – Kenneth Fisher Jun 18 '13 at 19:38
  • @AaronBertrand I don't think these will work if a value reappears: Fiddle – ypercubeᵀᴹ Jun 18 '13 at 19:44
13

Basically, this is @Taryn's suggestion "condensed" to a single SELECT with no derived tables:

SELECT DISTINCT
  Taco_ID,
  Taco_date = MAX(MIN(Taco_date)) OVER (PARTITION BY Taco_ID)
FROM Taco
GROUP BY
  Taco_ID,
  Taco_value
;

Note: this solution takes into account the stipulation that Taco_value can only increase. (More exactly, it assumes that Taco_value cannot change back to a previous value – same as the linked answer, in fact.)

A SQL Fiddle demo for the query: http://sqlfiddle.com/#!3/91368/2

|improve this answer|||||
7

You should be able to use both min() and max() aggregate functions get the result:

select t1.Taco_ID, MAX(t1.taco_date) Taco_Date
from taco t1
inner join
(
    select MIN(taco_date) taco_date,
        Taco_ID, Taco_value
    from Taco
    group by Taco_ID, Taco_value
) t2
    on t1.Taco_ID = t2.Taco_ID
    and t1.Taco_date = t2.taco_date
group by t1.Taco_Id

See SQL Fiddle with Demo

|improve this answer|||||
5

One more answer that is based on the assumption that the values do not reappear (this is basically @Aaron's query 2, condensed in one less nest):

;WITH x AS
(
  SELECT 
    Taco_ID, Taco_value, 
    Rn = ROW_NUMBER() OVER (PARTITION BY Taco_ID
                            ORDER BY MIN(Taco_date) DESC),
    Taco_date = MIN(Taco_date) 
  FROM dbo.Taco
  GROUP BY Taco_ID, Taco_value
)
SELECT Taco_ID, Taco_value, Taco_date
FROM x 
WHERE Rn = 1 ;

Test at: SQL-Fiddle


And an answer to the more general problem, where values can reappear:

;WITH x AS
(
  SELECT 
    Taco_ID, Taco_value, 
    Rn = ROW_NUMBER() OVER (PARTITION BY Taco_ID
                            ORDER BY MAX(Taco_date) DESC),    
    Taco_date = MAX(Taco_date) 
  FROM dbo.Taco
  GROUP BY Taco_ID, Taco_value
)
SELECT t.Taco_ID, Taco_date = MIN(t.Taco_date)
FROM x
  JOIN dbo.Taco t
    ON  t.Taco_ID = x.Taco_ID
    AND t.Taco_date > x.Taco_date
WHERE x.Rn = 2 
GROUP BY t.Taco_ID ;

(or using CROSS APPLY so all the related row, including the value, is shown):

;WITH x AS
(
  SELECT 
    Taco_ID, Taco_value, 
    Rn = ROW_NUMBER() OVER (PARTITION BY Taco_ID
                            ORDER BY MAX(Taco_date) DESC),    
    Taco_date = MAX(Taco_date) 
  FROM dbo.Taco
  GROUP BY Taco_ID, Taco_value
)
SELECT t.*
FROM x
  CROSS APPLY 
  ( SELECT TOP (1) *
    FROM dbo.Taco t
    WHERE t.Taco_ID = x.Taco_ID
      AND t.Taco_date > x.Taco_date
    ORDER BY t.Taco_date
  ) t
WHERE x.Rn = 2 ;

Test at: SQL-Fiddle-2

|improve this answer|||||
  • The suggestions for the more general problem do not work for IDs that have no changes. Could be fixed with adding dummy entries to the original set (something like dbo.Taco UNION ALL SELECT DISTINCT Taco_ID, NULL AS Taco_value, '19000101' AS Taco_date). – Andriy M Jun 20 '13 at 6:54
  • @AndriyM I know. I assumed that "change" means they want results when there are at least 2 values, the OP hasn't clarified that (and because it was easier to write :) – ypercubeᵀᴹ Jun 20 '13 at 6:56
2

FYI +1 for providing sample structure and data. The only thing I could have asked for is the expected output for that data.

EDIT: This one was going to drive me nuts. I just new there was a "simple" way to do this. I got rid of the incorrect solutions and put one I believe is correct. Here is a solution similar to @bluefeets but it covers the tests that @AaronBertrand gave.

;WITH TacoMin AS (SELECT Taco_ID, Taco_value, MIN(Taco_date) InitialValueDate
                FROM Taco
                GROUP BY Taco_ID, Taco_value)
SELECT Taco_ID, MAX(InitialValueDate)
FROM TacoMin
GROUP BY Taco_ID
|improve this answer|||||
  • 2
    The OP doesn't ask for more recent date, he asks when the value changes. – ypercubeᵀᴹ Jun 18 '13 at 19:22
  • Ahhh, I see my mistake. I worked out an answer, but it's pretty much the same as @Aaron's so no point in posting it. – Kenneth Fisher Jun 18 '13 at 19:48
1

Why not just get the difference of the lag value and the lead value? if the difference is zero it didn't change, it it's non zero then it changed. This can be done in a simple query:

-- example gives the times the value changed in the last 24 hrs
SELECT
    LastUpdated, [DiffValue]
FROM (
  SELECT
      LastUpdated,
      a.AboveBurdenProbe1TempC - coalesce(lag(a.AboveBurdenProbe1TempC) over (order by ProcessHistoryId), 0) as [DiffValue]
  FROM BFProcessHistory a
  WHERE LastUpdated > getdate() - 1
) b
WHERE [DiffValue] <> 0
ORDER BY LastUpdated ASC
|improve this answer|||||
  • The lag... analytical function was only "recently" introduced in SQL Server 2012. The original question is asking for a solution on SQL Server 2008 R2. Your solution wouldn't work for SQL Server 2008 R2. – John aka hot2use Sep 21 '18 at 9:43
0

Had a similar problem today - and in Power BI I could have solved it with the use of Tabler.FillDown. A bit of searching led me to a SQL variant of FillDown:

https://www.oraylis.de/blog/fill-down-table-in-t-sql-last-non-empty-value

So I took the time to adapt that solution to this example - with an extra row added to show reuse of Taco_value.

Insert INTO Taco 
Values (1, 1, '2012-07-01 00:00:09')

Note: this solution takes into account that Taco_value can increase AND decrease (or change back to previous value)

WITH help1
 AS (SELECT 
         *
        ,[ChangeIndicator] = CASE
                                 WHEN [Taco_value] = LAG([Taco_value],1) OVER(
                                      ORDER BY 
         [Taco_ID]) THEN 0
                                 ELSE 1
                             END
        ,[Taco_lag] = LAG([Taco_value],1) OVER(
         ORDER BY 
         [Taco_date])
     FROM [Taco]),
 help2
 AS (SELECT 
         *
        ,[RowGroup] = SUM([ChangeIndicator]) OVER(
         ORDER BY 
         [Taco_date])
     FROM [help1])
 SELECT 
     *
    ,[ChangeDate] = FIRST_VALUE([Taco_date]) OVER(PARTITION BY [RowGroup]
     ORDER BY 
     [Taco_date])
    ,[Taco_FillDown] = FIRST_VALUE([Taco_lag]) OVER(PARTITION BY [RowGroup]
     ORDER BY 
     [Taco_date])
 FROM [help2]
 ORDER BY 
     [Taco_date]

Result: enter image description here

|improve this answer|||||
-1

Could this be as simple as the following?

       SELECT taco_id, MAX(
             CASE 
                 WHEN taco_value <> MAX(taco_value) 
                 THEN taco_date 
                 ELSE null 
             END) AS last_change_date

Given that taco_value always increases?

p.s. I am quite beginner SQL myself, however, learning slowly but surely.

|improve this answer|||||
  • 1
    On SQL Server this gives the error. Cannot perform an aggregate function on an expression containing an aggregate or a subquery – Martin Smith Jun 19 '13 at 7:01
  • 2
    Adding a point to Martin's comment: you are on the safe side if you ever post tested code only. An easy way can be going to sqlfiddle.com if you are away from your usual playground. – dezso Jun 19 '13 at 8:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.