Christian Beikov Christian Beikov - 16 days ago 7
SQL Question

SQL Server order by with max rows in subquery

The following query works with PostgreSQL and I'd like to know why it doesn't work with SQL Server 2016.

select * from (values (1),(2)) as a(a)
UNION
(
select * from (values (1),(2)) as a(a)
order by 1 desc
offset 0 rows fetch first 1 rows only
)
order by 1 desc offset 0 rows fetch first 1 rows only


Can anyone explain to me why order by would not be supported here?

Funny that the following, which is another way to express this, works like a charm

select * from (values (1),(2)) as a(a)
where a.a in(
select * from (values (1),(2)) as a(a)
order by 1 desc
offset 0 rows fetch first 1 rows only
)
order by 1 desc offset 0 rows fetch first 1 rows only


Is this a bug?

Answer

The query after the union needs to be presented as a select, like so:

select * from (values (1),(2)) as a(a)
UNION
select * from
(
select * from (values (1),(2)) as a(a)
order by 1 desc
offset 0 rows fetch first 1 rows only
) b
order by 1 desc offset 0 rows fetch first 1 rows only
Comments