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?

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

    500*marks/100 as cash_price 
from students 
where marks<> @max_mark) as temp 
union all 
     s.student_id ,
     500 - temp1.total_cash_price
     student ,
     ( SELECT
           s.student_id ,
           sum(s.cash_price) total_cash_price
           temp t ,
           student s
           s.student_id = t.student_id
       GROUP BY
           s.student_id ) temp2
     marks = @max_mark

more ▼

asked Mar 29, 2010 at 06:39 AM in Default

avatar image

21 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Mar 29, 2010 at 06:46 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

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

Mar 29, 2010 at 06:58 AM ladybug

but if they are all based from the same 'base' data, then make that base data available in the original construct.

Mar 29, 2010 at 07:00 AM Kev Riley ♦♦

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

Mar 29, 2010 at 07:07 AM ladybug

can you give an example of the kind of thing you are trying to do, then we'll look at alternatives

Mar 29, 2010 at 07:22 AM Kev Riley ♦♦

at the worst case you could include all the SQL need for each part of the chain

Mar 29, 2010 at 08:02 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 29, 2010 at 06:39 AM

Seen: 4624 times

Last Updated: Mar 29, 2010 at 11:27 AM

Copyright 2018 Redgate Software. Privacy Policy