Not grouping through latest dates

I have 3 tables as follows:

BarcodeGroupMap

enter image description here

CategoryMaster

enter image description here

ProductMaster:

enter image description here

From these 3 tables i have made following query:

select bgm.BarcodeItem,
cm.CategoryID,cm.CategoryName,pm.ProductName,pm.ProductID,bgm.EffectFrom,bgm.Groups
from BarcodeGroupMap bgm,CategoryMaster cm,ProductMaster pm where
bgm.ProductID=pm.ProductID  and bgm.categoryID=cm.CategoryID

This query results into following:

enter image description here

Now, as we can see in query result barcode is repeating,

I just wanted to show one barcode once, according to Its latest Createddate from Barcodegroupmap table.

For this i made following query :

select bgm.BarcodeItem,
cm.CategoryID,cm.CategoryName,pm.ProductName,pm.ProductID,bgm.EffectFrom,bgm.Groups
from BarcodeGroupMap bgm,CategoryMaster cm,ProductMaster pm where
bgm.ProductID=pm.ProductID  and bgm.categoryID=cm.CategoryID and
bgm.BarcodeItem= select BarcodeItem from  BarcodeGroupMap bm1  where
CreatedDate=  (select top 1 CreatedDate from  BarcodeGroupMap bm2  
)order by bm1.BarcodeItem

But its not giving me correct result.

Please help me.

I just want to display one barcode item only one time according to its latested createddate in Barcodegroupmap.

Answer

I have no way of actually trying this query, because I do not have relevant tables and data, but this should get you started:

SELECT BarcodeItem, temp.CategoryID, cm.CategoryName, temp.ProductID, pm.ProductName, EffectFrom FROM ( 
  SELECT BarcodeItem, CategoryID, ProductID, CONVERT(date, EffectFrom) as EffectFrom,
    RANK() OVER (PARTITION BY BarcodeItem ORDER BY EffectFrom DESC) dest_rank
    FROM BarcodeGroupMap
  ) temp
  inner join CategoryMaster cm on cm.CategoryID = temp.CategoryID
  inner join ProductMaster pm on pm.ProductID = temp.ProductID
  where temp.dest_rank = 1

Leave a Reply

Your email address will not be published. Required fields are marked *