Getting values from two tables.
-
Hi, I have two tables like below. select childname,wgt from #Hy order by wgt desc select childname,ror from #Hy1 order by ror desc I need like below. #Hy.childname #HY.wgt #Hy1.childname #hy1.ror Just like appending the two tables without altering the result set means first one should be wgt desc and ror should be desc.
-
Hi, I have two tables like below. select childname,wgt from #Hy order by wgt desc select childname,ror from #Hy1 order by ror desc I need like below. #Hy.childname #HY.wgt #Hy1.childname #hy1.ror Just like appending the two tables without altering the result set means first one should be wgt desc and ror should be desc.
Do you mean that if the first query returns 10 rows and the second 20 rows, you will have only one result set, 30 rows? If that's the case you could use union and if you want the sorting to remain so that rows from first result set are first and then from the second one, you could have something like:
select 1, childname,wgt
from #Hy
union
select 2, childname,ror
from #Hy1
order by 1, 2 descThe need to optimize rises from a bad design.My articles[^]
-
Do you mean that if the first query returns 10 rows and the second 20 rows, you will have only one result set, 30 rows? If that's the case you could use union and if you want the sorting to remain so that rows from first result set are first and then from the second one, you could have something like:
select 1, childname,wgt
from #Hy
union
select 2, childname,ror
from #Hy1
order by 1, 2 descThe need to optimize rises from a bad design.My articles[^]
-
HI, It will just combine the values.I need in columns like #hy1.childname #hy1.ror #hy.childname #hy.wgt without altering the sorting.
AFAIK that won't be possible using a single query and it will be problematic if the result sets have different amount of rows. For example, if the first result set is 10 rows and the second one is 20 row, the only way that comes in mind is something like: - insert the first sorted result set (10 rows) to a temporary table - add two columns to the temporary table - update the existing 10 rows with the first 10 rows of the second sorted result set - insert the rest 10 rows from the second sorted result set to the table in the columns 3 and 4. However this will lead to a situation where you have 20 rows but the column 1 and 2 for the last 10 rows will be null. It makes me wonder what is the use for this kind of result.
The need to optimize rises from a bad design.My articles[^]