How to compare the latest two values for a given set of data

Question

I need a query that will give me the latest Author, Publisher, and Book for each author that published a book on the MAX(PublishDate) and compare the publisher from the latest 2 books per Author to see if it has changed. It does not have to be a single statement but can't create any physical tables.

declare @books TABLE(Author nvarchar(30), PublishDate datetime2(3), Publisher nvarchar(30), Title nvarchar(30))

INSERT INTO @books
VALUES  ('Author A', '15 June 2017 15:47', 'Publisher A', 'Book 1'),
        ('Author A', '15 May 2016 14:47', 'Publisher B', 'Book 2'),
        ('Author B', '15 May 2016 14:47', 'Publisher C', 'Book 3'),
        ('Author B', '15 April 2015 13:47', 'Publisher D', 'Book 4'),
        ('Author C', '15 June 2017 15:47', 'Publisher E', 'Book 5'),
        ('Author C', '15 May 2014 14:47', 'Publisher E', 'Book 6'),
        ('Author D', '15 June 2017 15:47', 'Publisher F', 'Book 7'),
        ('Author D', '15 May 2013 14:47', 'Publisher F', 'Book 8'),
        ('Author E', '15 June 2017 15:47', 'Publisher G', 'Book 9'),
        ('Author E', '15 May 2012 14:47', 'Publisher H', 'Book 10'),
        ('Author E', '15 April 2011 13:47', 'Publisher I', 'Book 11')


Output:  
Author         Publisher          PublisherChanged          Book
Author A       Publisher A        1                         Book 1
Author C       Publisher E        0                         Book 5
Author D       Publisher F        0                         Book 7
Author E       Publisher G        1                         Book 9

Answer 1

2008 R2

You'll have to create a relation on itself, I use row_number() here to basically join the table onto itself. You might want to use a temp table instead of a cte if it's a large table. I compare publisher with ranking number 1 with the publisher of ranking number 2:

declare @books table (Author nvarchar(30), PublishDate datetime2(3), Publisher nvarchar(30), Title nvarchar(30))

insert into @books
values  ('Author A', '15 June 2017 15:47', 'Publisher A', 'Book 1'),
        ('Author A', '15 May 2016 14:47', 'Publisher B', 'Book 2'),
        ('Author B', '15 May 2016 14:47', 'Publisher C', 'Book 3'),
        ('Author B', '15 April 2015 13:47', 'Publisher D', 'Book 4'),
        ('Author C', '15 June 2017 15:47', 'Publisher E', 'Book 5'),
        ('Author C', '15 May 2014 14:47', 'Publisher E', 'Book 6'),
        ('Author D', '15 June 2017 15:47', 'Publisher F', 'Book 7'),
        ('Author D', '15 May 2013 14:47', 'Publisher F', 'Book 8'),
        ('Author E', '15 June 2017 15:47', 'Publisher G', 'Book 9'),
        ('Author E', '15 May 2012 14:47', 'Publisher H', 'Book 10'),
        ('Author E', '15 April 2011 13:47', 'Publisher I', 'Book 11');

declare @max_date date = (
        select max(PublishDate)
        from @books
        );

with EvaluateChanges (
    Author
    ,PublishDate
    ,Publisher
    ,Title
    ,DateRanking
    )
as (
    select Author
        ,PublishDate
        ,Publisher
        ,Title
        ,row_number() over (partition by Author order by PublishDate desc)
    from @books
    )
select e1.Author
    ,e1.Publisher
    ,case when isnull(e2.Publisher, e1.Publisher) <> e1.Publisher then 1 else 0 end as PublisherChanged
    ,e1.Title
from EvaluateChanges as e1
left join EvaluateChanges as e2
    on e1.Author = e2.Author
        and e2.DateRanking = 2
where cast(e1.PublishDate as date) = @max_date
    and e1.DateRanking = 1;

2012 onwards

Here's a 2012 solution. I use the lead() function introduced in 2012 helps here to analyse the previous row for an author. The reason for the isnull is that lead() will null when there isn't a previous row to compare in the the partition. When it is null, I tell the evaluation to assume there was no change by assigning it the same value as Publisher for the current row context. I'm using a date variable for your max date and cast it as your fields are datetime.

declare @books table (Author nvarchar(30), PublishDate datetime2(3), Publisher nvarchar(30), Title nvarchar(30))

insert into @books
values  ('Author A', '15 June 2017 15:47', 'Publisher A', 'Book 1'),
        ('Author A', '15 May 2016 14:47', 'Publisher B', 'Book 2'),
        ('Author B', '15 May 2016 14:47', 'Publisher C', 'Book 3'),
        ('Author B', '15 April 2015 13:47', 'Publisher D', 'Book 4'),
        ('Author C', '15 June 2017 15:47', 'Publisher E', 'Book 5'),
        ('Author C', '15 May 2014 14:47', 'Publisher E', 'Book 6'),
        ('Author D', '15 June 2017 15:47', 'Publisher F', 'Book 7'),
        ('Author D', '15 May 2013 14:47', 'Publisher F', 'Book 8'),
        ('Author E', '15 June 2017 15:47', 'Publisher G', 'Book 9'),
        ('Author E', '15 May 2012 14:47', 'Publisher H', 'Book 10'),
        ('Author E', '15 April 2011 13:47', 'Publisher I', 'Book 11');

declare @max_date date = (
        select max(PublishDate)
        from @books
        );

with EvaluateChanges (
    Author
    ,PublishDate
    ,Publisher
    ,Title
    ,PublisherChanged
    )
as (
    select Author
        ,PublishDate
        ,Publisher
        ,Title
        ,case 
            when isnull(lead(Publisher) over (
                        partition by author order by PublishDate desc
                        ), Publisher) <> Publisher
                then 1
            else 0
            end
    from @books
    )
select Author
    ,Publisher
    ,PublisherChanged
    ,Title
from EvaluateChanges
where cast(PublishDate as date) = @max_date;

More reading on lead() and its cousin lag() here:

https://learn.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql?view=sql-server-2017 https://learn.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-2017