Do I need to use the dreaded sql server loop/ cursor for the result set I need?

Question

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.

Answer 1

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

Take a look at Sql Fiddle.

OLD answer:

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