I have a requirement like this
There are about 1.8 million records in a single table.
Consider i have 5 columns Col1,2.....5
I need to sort these columns based on a particular order
say col 1-4 ascending and 5th column descending (Say they are in groups of 15-25 records each)
After this sort i need to fetch the value of col5 in the First record and update the col5 for the rest of the records IN THAT GROUP.
Can someone provide an optimized way of doing this?
Thanks in advance
Do you not mean GROUP the data by the first 4 columns and update to the MIN of the 5th column?
set col5 =
select col1,col2,col3,col4,min(col5) col5
group by col1, col2, col3, col4
where yourtable.col1 = t.col1
and yourtable.col2 = t.col2
and yourtable.col3 = t.col3
and yourtable.col4 = t.col4