>Dear Experts
>
>Sir, I am SQLSERVER 2008 R2.
>Table has smalldatetime column and data looks like image1
>
>
>
>In original table, I want to regenerate sno column on date+party index like image2
>(sorted on date+party)
>
>
>
>Please help


sno is persistent or imaginary? If so you would need to an update query else a simple select would do.

sno doesn't exist as a column and generate on the fly:

   select [date], row_number() over (partition by cast([date] as date) order by cast([date] as date), party) as sno, party
   from myTable
   order by cast([date] as date), party

sno exists, update and return with updated values:

with mySort (date, sno, party) as 
(
   select [date], row_number() over (partition by cast([date] as date) order by cast([date] as date), party) as sno, party
   from myTable
)
update myTable
set sno = mySort.sno
from mySort 
where mySort.date = myTable.date and mySort.party = myTable.party;

select [date], sno, party
   from myTable
  order by cast([date] as date), sno