Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.)

share|improve this question
2  
I assume taco has nothing to do with the food? –  Kermit Jun 18 '13 at 19:19
4  
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
6  
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
add comment

6 Answers

up vote 9 down vote accepted

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

share|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
show 2 more comments

Basically, this is @bluefeet'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

share|improve this answer
2  
Whoa, nested MAX/MIN. MIND BLOWN +1 –  Aaron Bertrand Jun 19 '13 at 11:16
add comment

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

share|improve this answer
add comment

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

share|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
add comment

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
share|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
add comment

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.

share|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
    
Plus, you usually need a FROM clause in sql queries. –  ypercube Jun 19 '13 at 9:09
1  
@pmc086 This is a difficult question/query and +1 for being brave enough to try it. You can try to edit the answer so it is correct or delete it. And don't worry about the -1 you got, downvotes are aimed to (wrong) answers, not to users. You are welcome to try some other questions on the site that are not so advanced. –  ypercube Jun 20 '13 at 7:02
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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