Do I need to use the dreaded sql server loop/ cursor for the result set I need?
I need a sql server result set that "breaks" on a column value, but if I order by this column in a ranking function, the order I really need is lost. This is best explained by example. The query I'm currently experimenting with is:
select RANK() over(partition by Symbol, Period order by TradeDate desc) SymbSmaOverUnderGroup, Symbol, TradeDate, Period, Value, Low, LowMinusVal, LMVSign from #smasAndLow3
and it returns:
Rnk Symbol TradeDate Period Value Low LowMinusVal LMVSign 1 A 9/6/12 5 37.09 36.71 -.38 U 2 A 9/5/12 5 37.03 36.62 -.41 U 3 A 9/4/12 5 37.07 36.71 -.36 U 4 A 8/31/12 5 37.15 37.30 .15 O 5 A 8/30/12 5 37.22 37.40 .18 O 6 A 8/29/12 5 37.00 36.00 -1.00 U 7 A 8/28/12 5 37.10 37.00 -.10 U
The rank I need here is: 1,1,1,2,2,3,3. So I need to partition by Symbol, Period, and I need to start a new partition on LMVSign (which only contains the values U, O, and E), but it's essential that I order by TradeDate desc. Unless I'm mistaken, partitioning or ordering by LMVSign will make it impossible to sort on the date column. I hope this makes sense. I'm working like mad to do this without a cursor, but I can't get it to work.. thanks in advance.
UPDATE after clarification: I think that you are entering the world of islands and gaps. If your requirement is to group rows by Symbol, Period and LMVSign ordered descendingly by TradeDate, ranking them when any one of these columns change, you might use this (by Itzik Ben-Gan's solution to islands and gaps).
; with islandsAndGaps as ( select *, -- Create groups. Important part is order by -- The difference remains the same as two sequences -- run along, but the number itself is not ordered row_number() over (partition by Symbol, Period order by TradeDate) - row_number() over (partition by Symbol, Period order by LMVSign, TradeDate) grp from Table1 ), grouped as ( select *, -- So to order it we use last date in group -- (mind partition by uses changed order by from second row_number -- and unordered group number max(TradeDate) over(partition by LMVSign, grp) DateGroup from islandsAndGaps ) -- now we can get rank select dense_rank() over (order by DateGroup desc) Rnk, * from grouped order by TradeDate desc
Partition by restarts ranking. I think that you need order by:
dense_rank() over (order by Symbol, Period, LMVSign desc) Rnk
and then you should use TradeDate in order by:
order by Rnk, TradeDate desc
If you need it as a number, add another column:
row_number() over (order by Symbol, Period, LMVSign desc, TradeDate desc) rn