Determine last updated row in a group when using multiple joins

Question

I have the following data: SQL Fiddle (Schema)

/* Item */
CREATE TABLE [dbo].[Item](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](30) NOT NULL CONSTRAINT [DF_Item_Description]  DEFAULT (''),
    [ItemLookupCode] [nvarchar](25) NOT NULL CONSTRAINT [DF_Item_ItemLookupCode]  DEFAULT (''),
    [Price] [money] NOT NULL CONSTRAINT [DF_Item_Price]  DEFAULT (0),
    [LastUpdated] [datetime] NOT NULL CONSTRAINT [DF_Item_LastUpdated]  DEFAULT (getdate()),
    [DateCreated] [datetime] NOT NULL CONSTRAINT [Df_Item_DateCreated]  DEFAULT (getdate()),
 CONSTRAINT [PK_Item] PRIMARY KEY NONCLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO [dbo].[Item] (
    [Description],
    [ItemLookupCode],
    [Price],
    [LastUpdated]
)
VALUES
  ('Product A1', 'PA1', 1, '03/01/2016 12:00:00'),
  ('Product A2', 'PA2', 2, '02/01/2016 12:00:00'),
  ('Product A3', 'PA3', 3, '01/01/2016 12:00:00'),

  ('Product B3', 'PB3', 3, '02/01/2016 12:00:00'),
  ('Product B2', 'PB2', 2, '03/01/2016 12:00:00'),
  ('Product B1', 'PB1', 1, '01/01/2016 12:00:00'),

  ('Product C2', 'PC2', 2, '02/01/2016 12:00:00'),
  ('Product C1', 'PC1', 1, '01/01/2016 12:00:00'),
  ('Product C3', 'PC3', 3, '03/01/2016 12:00:00')

GO

/* ItemClass */
CREATE TABLE [dbo].[ItemClass](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](30) NOT NULL CONSTRAINT [DF_ItemClass_Description]  DEFAULT (''),
    [ItemLookupCode] [nvarchar](25) NOT NULL CONSTRAINT [Df_ItemClass_ItemLookupCode]  DEFAULT (''),
    [Price] [money] NOT NULL CONSTRAINT [DF_ItemClass_Price]  DEFAULT ((0)),
 CONSTRAINT [PK_ItemClass] PRIMARY KEY NONCLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO [dbo].[ItemClass] (
    [Description],
    [ItemLookupCode],
    [Price]
)
VALUES
  ('ItemClass A', 'ICA', 1),
  ('ItemClass B', 'ICB', 2),
  ('ItemClass C', 'ICC', 3)

GO

/* ItemClassComponent */
CREATE TABLE [dbo].[ItemClassComponent](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ItemClassID] [int] NOT NULL CONSTRAINT [DF_ItemClassComponent_ItemClassID]  DEFAULT (0),
    [ItemID] [int] NOT NULL CONSTRAINT [DF_ItemClassComponent_ItemID]  DEFAULT (0),
    [Price] [money] NOT NULL CONSTRAINT [Df_ItemClassComponent_Price]  DEFAULT (0),
 CONSTRAINT [PK_ItemClassComponent] PRIMARY KEY NONCLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO [dbo].[ItemClassComponent] (
    [ItemClassID],
    [ItemID],
    [Price]
)
VALUES
  (1, 1, 1),
  (1, 2, 1),
  (1, 3, 1),
  (2, 6, 1),
  (2, 5, 1),
  (2, 4, 1),
  (3, 8, 1),
  (3, 7, 1),
  (3, 9, 1)
GO

I am trying to return the data from dbo.Item for each product that is the most recently updated in it's respective group (Defined in dbo.ItemClassComponent).

I have gotten pretty close, but my query returns duplicate rows! So close... Can anyone steer me in the right direction, please? Because- TBH I have no idea why I am seeing duplicate rows returned lol.

My attempt: SQL Fiddle

SELECT DISTINCT
    ic.Description
    ,i.ID
    ,i.Description
    ,i.ItemLookupCode
    ,i.Price
    ,i.LastUpdated
FROM Item i
INNER JOIN ItemClassComponent icc
    ON icc.ItemID = i.ID
INNER JOIN ItemClass ic
    ON ic.ID = icc.ItemClassID
INNER JOIN (
    SELECT
        MAX(_i.LastUpdated) AS LastUpdated
    FROM Item _i
    JOIN ItemClassComponent _icc
        ON _icc.ItemID = _i.ID
    GROUP BY _icc.ItemClassID
) lu
    ON lu.LastUpdated = i.LastUpdated;

Note: I would prefer not to just slap DISTINCT in there...

Answer 1

I'm pretty sure you just need to compare the item classes as well as the last updated date:

SELECT . . .
FROM Item i INNER JOIN
     ItemClassComponent icc
     ON icc.ItemID = i.ID INNER JOIN
     ItemClass ic
     ON ic.ID = icc.ItemClassID INNER JOIN
     (SELECT _icc.ItemClassID, MAX(_i.LastUpdated) AS LastUpdated
      FROM Item _i JOIN
           ItemClassComponent _icc
           ON _icc.ItemID = _i.ID
      GROUP BY _icc.ItemClassID
     ) lu
    ON lu.LastUpdated = i.LastUpdated AND lu.ItemClassId = ic.Id;

Normally, I would write this using window functions:

with cte as (
      SELECT . . .,
             ROW_NUMBER() OVER (PARTITION BY icc.ItemClassId ORDER BY i.LastUpdated DESC) as seqnum
      FROM Item i INNER JOIN
           ItemClassComponent icc
           ON icc.ItemID = i.ID INNER JOIN
           ItemClass ic
           ON ic.ID = icc.ItemClassID 
    )
select *
from cte
where seqnum = 1;