Excentric shoot it later

Use of Rank function to extract a list of rows with the highest date

Today I had the problem to create a query to get the most recent price from a pricelists table. Every pricelist in the table can contain the same article any number of times with different startup dates. I made some search to find a way to obtain what i needed and I found the RANK function, it was used in a similar scenario. I publish this post as usual to remember how to use this function next time I need it and in the hope to help someone else solving similar problems.

The table:

CREATE TABLE [dbo].[TbPricelistsRg](
    [IDPriceListRg] [int] NOT NULL,
    [IDPriceList] [nvarchar](10) NOT NULL,
    [IDArticle] [nvarchar](32) NULL,
    [Price] [decimal](18, 5) NULL,
    [StartDate] [date] NULL,
 CONSTRAINT [PK_TbPriceListsRg] PRIMARY KEY CLUSTERED 
(
    [IDPriceListRg] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  
     = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Every row is linked to a PriceList through the IDPriceLIst field, we don’t need to use it for the example so it is omitted.

INSERT INTO [dbo].[TbPricelistsRg]
           ([IDPriceList]
           ,[IDArticle]
           ,[Price]
           ,[StartDate])
     VALUES
           ('CLD'
           ,'ABC'
           ,10
           ,CONVERT(date, '20080105', 112))
GO

INSERT INTO [dbo].[TbPricelistsRg]
           ([IDPriceList]
           ,[IDArticle]
           ,[Price]
           ,[StartDate])
     VALUES
           ('CLD'
           ,'OCQ'
           ,14
           ,CONVERT(date, '20080105', 112))
GO

INSERT INTO [dbo].[TbPricelistsRg]
           ([IDPriceList]
           ,[IDArticle]
           ,[Price]
           ,[StartDate])
     VALUES
           ('CLD'
           ,'FYS'
           ,21
           ,CONVERT(date, '20080105', 112))
GO

INSERT INTO [dbo].[TbPricelistsRg]
           ([IDPriceList]
           ,[IDArticle]
           ,[Price]
           ,[StartDate])
     VALUES
           ('CLD'
           ,'MUI'
           ,13
           ,CONVERT(date, '20080105', 112))
GO

INSERT INTO [dbo].[TbPricelistsRg]
           ([IDPriceList]
           ,[IDArticle]
           ,[Price]
           ,[StartDate])
     VALUES
           ('CLD'
           ,'SIO'
           ,42
           ,CONVERT(date, '20080105', 112))
GO

INSERT INTO [dbo].[TbPricelistsRg]
           ([IDPriceList]
           ,[IDArticle]
           ,[Price]
           ,[StartDate])
     VALUES
           ('CLD'
           ,'GOU'
           ,18
           ,CONVERT(date, '20080105', 112))
GO

INSERT INTO [dbo].[TbPricelistsRg]
           ([IDPriceList]
           ,[IDArticle]
           ,[Price]
           ,[StartDate])
     VALUES
           ('CLD'
           ,'HOU'
           ,20
           ,CONVERT(date, '20080105', 112))
GO

The previous script inserts some articles, we can repeat it changing the PriceLIst, the Dates and prices to be able to show how the query meets our needs.

SELECT [IDPriceListRg]
      ,[IDPriceList]
      ,[IDArticle]
      ,[Price]
      ,[StartDate]
      ,RANK() OVER (PARTITION BY IDPriceList, IDArticle ORDER BY StartDate DESC) DATERANK
  FROM [paperinik].[dbo].[TbPricelistsRg]

The RANK function, allows us to indicate one or more grouping fields on which calculate ranking, this is done using the PARTITION BY keywords, in our query we indicate the two fields on which ranking has to break. The ORDER BY  keyword, instead indicates on which field we have to measure ranking. while the DESC keywords tells that we want to start from the most recent date..

This is the result of our query.

So to obtain the most recent prices, we need just to add a filter on DATERANK = 1, and to obtain the less recent price we just need to delete the DESC keyword and filter on rank 1.

Print | posted on mercoledì 7 luglio 2010 23.45

Copyright © Sabrina C.

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski