如何修复SQL select union的不确定TOP 1结果

时间:2021-07-02 15:46:30

I am selecting an int value from two different tables as shown below

我从两个不同的表中选择一个int值,如下所示

select col1 from tablea
union 
select col1 from tableb

The requirement is if result is found in first query, use that; otherwise, look in second table.

要求是在第一个查询中找到结果,使用它;否则,请查看第二个表格。

select top 1 * from (select col1 from tablea union select col1 from tableb) as a

Problem is the highest numerical value of the result set is returned as top 1 - not the first result found.

问题是结果集的最高数值返回为前1 - 而不是找到的第一个结果。

I don't care about the numerical value as in it's order - I just want to apply precedence to if find a value from select 1 don't bother to run second query.

我不关心它的顺序中的数值 - 我只想应用优先级如果从select 1中找到一个值而不打扰运行第二个查询。

Without the top 1 * I get returned 3 and 6. When I do top 1 * I get 6 and same result if I do the other select first.

没有前1 *我得到返回3和6.当我做前1 *我得到6和相同的结果如果我做另一个选择第一。

Help!

1 个解决方案

#1


3  

When you UNION these two selects the order IS NOT DEFINED you can think of it as a RANDOM order. So you should define order to get right results. For example:

当您使用UNION这两个选择顺序IS NOT DEFINED时,您可以将其视为RANDOM顺序。因此,您应该定义顺序以获得正确的结果。例如:

select top 1 C1 from 
(select col1 as C1, 1 as c2 from tablea 
     union 
 select col1 as C1, 2 as c2 from tableb) as a 
ORDER BY C2

#1


3  

When you UNION these two selects the order IS NOT DEFINED you can think of it as a RANDOM order. So you should define order to get right results. For example:

当您使用UNION这两个选择顺序IS NOT DEFINED时,您可以将其视为RANDOM顺序。因此,您应该定义顺序以获得正确的结果。例如:

select top 1 C1 from 
(select col1 as C1, 1 as c2 from tablea 
     union 
 select col1 as C1, 2 as c2 from tableb) as a 
ORDER BY C2