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

Question

question

ladybug asked Mar 29, '10

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

(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

sql-server-2005 union

10 |1200

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

Answer 1 · 2010-03-29T06:46:39Z

Kev Riley answered Mar 29, '10

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.

11

10 |1200

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

ladybug commented · Mar 29, 2010 at 06:58 AM

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 ·

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

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

0 ·

ladybug commented · Mar 29, 2010 at 07:07 AM

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 ·

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

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

0 ·

Kev Riley ♦♦ commented · Mar 29, 2010 at 08:02 AM

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

0 ·

Show more comments

question