row number over partition in sql server 2008 R2

Question

I used the following query to get the result as

Select * from
          (
          Select M.MaterialName,L.LocationName,S.Balance,S.StockDate,ROW_NUMBER()
          over(Partition by L.LocationName,M.MaterialName order by S.StockDate desc)RN from
          StockRegister S join Locations L on S.LocationId=L.LocationId join                 
          ElectricalProducts M on M.PartNo=S.PartNo 
          where S.StockDate<=convert(date,'01-02-2014',103) and      
          substring(S.PartNo,1,2)='01' and SUBSTRING(S.PartNo,9,3)='002'
          )sr

the result is

    Material Name           Location        Qty     StockDate      RN 
    Tube LightPhilips40W    ShozhingaNal    200    2014-01-31      1
    Tube LightPhilips40W    Kottivakkam     200    2014-01-31      1
    Tube LightPhilips40W    Kottivakkam     150    2014-01-31      2
    Tube LightPhilips40W    MAChidambaramm  50     2014-01-31      1

from that i have to get the only the latest entry as follows

       Material Name           Location       Qty     StockDate      RN     
       Tube LightPhilips40W    ShozhingaNal   200     2014-01-31     1
       Tube LightPhilips40W    Kottivakkam    150     2014-01-31     2
       Tube LightPhilips40W    MAChidambaramm  50     2014-01-31     1

How to get it?

Answer 1

Try Like this

Use COUNT(*) OVER(Partition by L.LocationName,M.MaterialName ) CNT this to find latest row

 SELECT * FROM
             (
              SELECT M.MaterialName,L.LocationName,S.Balance,S.StockDate,
              ROW_NUMBER() OVER(Partition by L.LocationName,M.MaterialName order by S.StockDate DESC)RN
              COUNT(*) OVER(Partition by L.LocationName,M.MaterialName ) CNT
              FROM StockRegister S JOIN Locations L ON
                                 S.LocationId=L.LocationId 
              JOIN ElectricalProducts M ON
                                 M.PartNo=S.PartNo 
              WHERE S.StockDate <= convert(date,'01-02-2014',103) AND 
              SUBSTRING(S.PartNo,1,2)='01' AND
              SUBSTRING(S.PartNo,9,3)='002'
             )AS S 
             WHERE RN = CNT

Or else try below method

Since you are ordering StockDate in RowNumber by DESC, So latest RowNumber becomes 1. Hence you can select it by WHERE RN = 1

         SELECT * FROM
         (
          SELECT M.MaterialName,L.LocationName,S.Balance,S.StockDate,
          ROW_NUMBER() OVER(Partition by L.LocationName,M.MaterialName order by S.StockDate DESC)RN 
          FROM StockRegister S JOIN Locations L ON
                             S.LocationId=L.LocationId 
          JOIN ElectricalProducts M ON
                             M.PartNo=S.PartNo 
          WHERE S.StockDate <= convert(date,'01-02-2014',103) AND 
          SUBSTRING(S.PartNo,1,2)='01' AND
          SUBSTRING(S.PartNo,9,3)='002'
         )AS S WHERE RN = 1