Sql Query: Union All with Top

Question

I want to get a combined output from two select statements.

First select returns the object of a certain index. Second select returns the closest Object.

  declare @h geometry select @h = Geom from obj where ObjectIndex = 15054
  select ObjectIndex, Geom.STDistance(@h) from obj where ObjectIndex = 15054 
  union all
  select top( 1) ObjectIndex, Geom.STDistance(@h) from  obj WITH(index(idx_Spatial)) where Geom.STDistance(@h) < 0.0004 
  and ObjectLayerName = 'Up_Layer' order by Geom.STDistance(@h) 

Unfortunately, the second statement returns the wrong object. I expected the closest object, but it returns the second closest instance. However, when I only perform the second select statement it returns the right object.

 select top( 1) ObjectIndex, Geom.STDistance(@h) from  obj WITH(index(idx_Spatial)) where Geom.STDistance(@h) < 0.0004 
  and ObjectLayerName = 'Up_Layer' order by Geom.STDistance(@h) 

Thanks for help.

Answer 1

Order by will be applied after the UNION, try something like this

DECLARE @h GEOMETRY

SELECT @h = Geom
FROM   obj
WHERE  ObjectIndex = 15054

SELECT ObjectIndex,
       Geom.Stdistance(@h) dist
FROM   obj
WHERE  ObjectIndex = 15054
UNION ALL
SELECT ObjectIndex,
       dist
FROM  (SELECT TOP( 1) ObjectIndex,
                      Geom.Stdistance(@h) AS dist
       FROM   obj WITH(INDEX(idx_Spatial))
       WHERE  Geom.Stdistance(@h) < 0.0004
              AND ObjectLayerName = 'Up_Layer'
       ORDER  BY Geom.Stdistance(@h)) a
ORDER  BY dist