title: Need SQL query to generate order id based on dates
body: <p>I am trying to write a SQL query to generated orderid for the data that looks like</p>
  
  <pre><code> ID SYMBOL   Date          OrderID
   112    GR   24-Jan-13     0
   112    FIX  31-Aug-11     0
   112    FIX  29-Sep-11     0
   112    FIX  17-Nov-11     0
   112    MIN  19-Dec-08     0
   112    MIN  05-Feb-09     0
   445    GR   24-Jan-13     0
   445    GR   25-Jul-13     0
   445    FIX  31-Aug-11     0
   445    FIX  29-Sep-11     0
   445    FIX  17-Nov-11     0
  </code></pre>
  
  <p>Need Output as :</p>
  
  <pre><code>ID  SYMBOL  Date        OrderID
  112 GR      24-Jan-13   1
  112 GR      25-Jul-13   2
  112 FIX     31-Aug-11   1
  112 FIX     29-Sep-11   2
  112 FIX     17-Nov-11   3
  112 MIN     19-Dec-08   1
  112 MIN     05-Feb-09   2
  445 GR      24-Jan-13   1
  445 GR      25-Jul-13   2
  445 FIX     31-Aug-11   1
  445 FIX     29-Sep-11   2
  445 FIX     17-Nov-11   3
  </code></pre>
  
  <p>Queries:
  my attemp:</p>
  
  <pre><code>;with ctes  (ID, SYMBOL, date, orderid)as
   (
      select ID, SYMBOL, date , orderid from #temp
  
  Union all
  
  select  m.ID, m.SYMBOL, m.date, t.orderid + 1
  from ctes t 
  inner join #temp m on t.ID = m.id 
  and t.symbol = m.symbol
  
    )select  * from ctes
  
  
  
  create table #temp (id int, SYMBOL varchar(5), [Date] datetime, orderid int)
  insert into #temp (id, symbol, [date], orderid)
  values(112, 'GR',   '24-Jan-13',    0) 
  insert into #temp (id, symbol, [date], orderid)
  values(112,'GR', '25-Jul-13',   0)
  insert into #temp (id, symbol, [date], orderid)
  values(112, 'FIX',  '31-Aug-11' ,   0)
  insert into #temp (id, symbol, [date], orderid)
  values(112, 'FIX',  '29-Sep-11' ,   0)
  insert into #temp (id, symbol, [date], orderid)
  values(112, 'FIX',  '17-Nov-11' ,   0)
  insert into #temp (id, symbol, [date], orderid)
  values(112, 'FIX',  '14-Jan-13' ,   0)
  insert into #temp (id, symbol, [date], orderid)
  values(112, 'MIN',  '19-Dec-08' ,   0)
  insert into #temp (id, symbol, [date], orderid)
  values(112, 'MIN',  '05-Feb-09' ,   0)
  insert into #temp (id, symbol, [date], orderid)
  values(112, 'MIN',  '19-Aug-09' ,   0)
  insert into #temp (id, symbol, [date], orderid)
  values(112, 'MIN',  '19-Sep-13' ,   0)
  insert into #temp (id, symbol, [date], orderid)
  values(445, 'GR',   '24-Jan-13' ,   0)
  insert into #temp (id, symbol, [date], orderid)
  values(445, 'GR',   '25-Jul-13' ,   0)
  insert into #temp (id, symbol, [date], orderid)
  values(445, 'FIX',  '31-Aug-11' ,   0)
  insert into #temp (id, symbol, [date], orderid)
  values(445, 'FIX',  '29-Sep-11' ,   0)
  insert into #temp (id, symbol, [date], orderid)
  values(445, 'FIX',  '17-Nov-11',    0)
  </code></pre>