Can SQL Server limit results in query based on rank without using cte?

Question

I have been able to successfully use a cte with below SQL that contains rank that gives me output.

WITH cte AS (
   SELECT foo, bar,
   rank() OVER(PARTITION BY foo ORDER BY bar) AS [rank]
   FROM baz)
select * from cte where [rank]=1

My question is can this be done WITHOUT using a cte?

You can do it with a subquery - but essentially, it's the same — marc_s, Mar 17 '15 at 19:59
you can use a derived table instead, but that's the same. Starting from SQL Server 2012, you could use `ORDER BY...FETCH` — Lamak, Mar 17 '15 at 19:59
@Lamak `ORDER BY ... OFFSET ... FETCH` doesn't really help. This is a [greatest n per group](http://stackoverflow.com/questions/tagged/greatest-n-per-group) problem, not a windowing problem. — Bacon Bits, Mar 17 '15 at 20:07
Can you tell us why you don't want to use CTE... esp. if it works. it could help us suggest an alternative approach. — G B, Mar 17 '15 at 20:16
Why do you want to do this without a cte/subquery? If you just want the top 1 and the ordering is as simple as in the question then you can do it using `NOT EXISTS` or `LEFT JOIN/IS NULL`, e.g. SELECT * FROM Baz AS b WHERE NOT EXISTS (SELECT 1 FROM Baz AS b2 WHERE b2.foo = b.foo AND b2.bar > b.bar)`, or `SELECT b.* FROM Baz AS b LEFT JOIN Baz AS b2 ON b2.foo = b.foo AND b2.bar > b.bar WHERE b2.foo IS NULL` - This seems a bit like code golf though. — GarethD, Mar 17 '15 at 20:16
My FROM is much, much longer. Combines about 10 tables. So recreating the select with not exists would not be good for mainteance. In that case, I would rather stick to using cte. I just felt like maybe there was something similar to HAVING clause for dealing with rank(). — JeffJak, Mar 17 '15 at 20:59
In the future, you should *include that background information in the question*. Window functions and functions with the `OVER()` clause are [defined as executing after the `WHERE`, `GROUP BY`, or `HAVING` clauses](http://stackoverflow.com/a/14114500/696808). That's why they're not allowed in those clauses; they have to be calculated after them. So, in order to filter a window function, you need to use a subquery or CTE. There is no filtering clause in any RDBMS or in ANSI syntax that does this for you. — Bacon Bits, Mar 18 '15 at 15:23

score -1 · Answer 1 · edited May 23 '17 at 11:56

You can use OUTER APPLY as a correlated subquery. This is also known as a LATERAL join or view in some RDBMSs (Oracle, PostgreSQL). It looks like a LEFT JOIN, but it generally performs much better.

Real life example, when to use OUTER / CROSS APPLY in SQL

SELECT pr.name,
       pa.name
FROM   sys.procedures pr
       OUTER APPLY (SELECT TOP 2 *
                    FROM   sys.parameters pa
                    WHERE  pa.object_id = pr.object_id
                    ORDER  BY pr.name) pa
ORDER  BY pr.name,
          pa.name

That question links to an article with several solutions for the "greatest N per group" problem. It doesn't format well for me, but it does seem to be a good article otherwise.

Can SQL Server limit results in query based on rank without using cte?

1 Answers1