# question

## Using the result of first select statement in second select in UNION ALL

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?

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
``````

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·

A few options here.

use a cte to define `temp` then reference it as many times as you want in the union

``````;with temp_cte(a,b) as (select a, b from tbl1)
select a,b from temp_cte
union all
select otherstuff from temp_cte
``````

use a table variable `@temp`, or temporary table `#temp` to define `temp`

``````declare @temp table (a,b)
insert into @temp select a,b from tbl1
select a,b from @temp
union all
select otherstuff from @temp
``````

use the definition over and over again

``````(select a, b from tbl1)
union all
(select a, b from tbl1)
``````

OK now we have some sample data

``````declare @students table (studentid int, marks int)
insert into @students
select 1, 10
union   select 2, 15
union   select 3, 20
union   select 4, 50
``````

we want to split \$500 across these (according to this function 500*mark/100), with the remainder going to the highest mark

so from this we can see that student 4 gets \$250, plus the remaining \$25

cte method

``````;with cte (studentid, cash) as (
select
studentid,
500*marks/100 as cash
from @students
)

select studentid, cash from cte
union all
select studentid, 500 - (select sum(cash) from cte)
from cte
where studentid = (select top 1 studentid from cte order by cash desc)
``````

now I don't actually think that doing this with a UNION ALL is the best way, this should be done all in one query, but this shows you how to do what you where asking in the way you wanted.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·
Thank You Kev.. But, I have an issue here. It goes like a chain.. 2nd SELECT requires result from 1st SELECT 3rd SELECT requires result from 2nd SELECT ....
0 Likes 0 ·
·
but if they are all based from the same 'base' data, then make that base data available in the original construct.
0 Likes 0 ·
·
each select statement actually performs some calculation first, and then selects the data.. this calculated data is used for the second level calculation in the second select query, and this goes on.. Thank You
0 Likes 0 ·
·
can you give an example of the kind of thing you are trying to do, then we'll look at alternatives
0 Likes 0 ·
·
at the worst case you could include all the SQL need for each part of the chain
0 Likes 0 ·