derekmx271 derekmx271 - 2 months ago 5
SQL Question

Determine last updated row in a group when using multiple joins

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

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;