HI,
select
ROW_NUMBER()
OVER (PARTITION BY Secid
ORDER BY secid, PriceDate desc, price) AS 'RowNumber',* from
(
select secid, PriceSource, RefPriceSource, PriceDate, Price,
Dense_Rank() over (Partition by Price,secid order by secid, PriceDate desc, price) as 'DRANK'
from secPrice where priceDate <= '02/05/2007' and secid in (49)--select secid from #temp2 where noofdays is null)
) aa
if i run the above query I am getting the resultset as below.
rownumber secid pricesource refpricesource pricedate price drank
1 49 6789 6794 2007-02-05 1 1
2 49 6789 6794 2007-02-02 1 2
3 49 6789 6794 2007-02-03 1 3
In this I want to take max(drank) value that is where drank is 3
can anybody helpme out in this pl.
select
ROW_NUMBER()
OVER (PARTITION BY Secid
ORDER BY secid, PriceDate desc, price) AS 'RowNumber',* from
(
select secid, PriceSource, RefPriceSource, PriceDate, Price,
Dense_Rank() over (Partition by Price,secid order by secid, PriceDate desc, price) as 'DRANK'
from secPrice where priceDate <= '02/05/2007' and secid in (49)--select secid from #temp2 where noofdays is null)
) aa
if i run the above query I am getting the resultset as below.
rownumber secid pricesource refpricesource pricedate price drank
1 49 6789 6794 2007-02-05 1 1
2 49 6789 6794 2007-02-02 1 2
3 49 6789 6794 2007-02-03 1 3
In this I want to take max(drank) value that is where drank is 3
can anybody helpme out in this pl.
