I will try to make my question clear with a simple eg.
(select a, b from tbl1) as temp union all select * from temp
As alias is not valid in the second select statements, what's the way to achieve it?
Edit (from comments)
Suppose i've a table - Students with fields student_id, marks. I want to distribute Rs.500 among students depending on their marks. I want to give the remaining money to the student having max mark (it's done in second select). suppose @max_mark = max mark of the student
(select student_id, 500*marks/100 as cash_price from students where marks<> @max_mark) as temp union all SELECT s.student_id , 500 - temp1.total_cash_price FROM student , ( SELECT s.student_id , sum(s.cash_price) total_cash_price FROM temp t , student s WHERE s.student_id = t.student_id GROUP BY s.student_id ) temp2 WHERE marks = @max_mark