What should be the alternative for lead and lag for SQL Server 2000 to 2008

Question

What could be the alternative for lead and lag for SQL Server 2000 to 2008? I am trying to get previous and next invoice number to given invoice number, i.e 5

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
};

Answer 1

this worked for me

"WITH CTE AS(
                       SELECT rownum = ROW_NUMBER() OVER(ORDER BY p.InvNo),
                       p.InvNo FROM Invoice1 p
                     )
                   SELECT
                   prev.InvNo prev_invNo,
                       CTE.InvNo,
                       nex.InvNo next_invNo
                   FROM CTE
                   LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
                   LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1
                   where CTE.InvNo = " + invoiceNumber + ""