title: What should be the alternative for lead and lag for SQL Server 2000 to 2008
body: <p>What could be the alternative for <code>lead</code> and <code>lag</code> for SQL Server 2000 to 2008? I am trying to get previous and next invoice number to given invoice number, 
  i.e 5</p>
  
  <pre><code>SqlDataAdapter invoiceAdptr = new SqlDataAdapter(@"
                   select t.prev_invNo, t.InvNo, t.next_invNo
                   from (select 
                             lag(InvNo) over (order by id) as prev_invNo,
                             InvNo,
                             lead(InvNo) over (order by id) as next_invNo
                         from Invoice1) t
                   where t.InvNo = " + invoiceNumber + "", con);
  
  DataTable invoiceDataTable = new DataTable();
  invoiceAdptr.Fill(invoiceDataTable);
  
  var invoices = new Invoices()
  {
      PreviousInvoice = Convert.ToString(invoiceDataTable.Rows[0]["prev_invNo"]),
      NextInvoice = Convert.ToString(invoiceDataTable.Rows[0]["next_invNo"]),
      CurrentInvoice = invoiceNumber
  };
  </code></pre>