Strictly speaking, that limitation makes sense because the above
-- Get arbitrarily numbered row_numbers SELECT ROW_NUMBER() OVER () -- Skip arbitrary rows SELECT a FROM (VALUES (1), (2), (3), (4)) t(a) OFFSET 3 ROWS
OFFSETexpressions are non-deterministic. Two subsequent executions of the same query might produce different results. But then again, any
ORDER BYclause is non-deterministic, if you do not order by a strictly
UNIQUEexpression, such as a primary key. So, that’s a bit of a pain, because other databases aren’t that strict and after all, you might just not care about explicit ordering for a quick, ad-hoc query, so a “reasonable”, lenient default would be useful.
Constant ORDER BY clauses don’t workYou cannot add a constant
ORDER BYclause to window functions either. I.e.:
-- This doesn't work: SELECT ROW_NUMBER() OVER (ORDER BY 'a') -- But this does! SELECT a FROM (VALUES (1), (2), (3), (4)) t(a) ORDER BY 'a' OFFSET 3 ROWS
ORDER BY 'a'uses a constant
VARCHARexpression, not a numeric one, as that would be generating column-reference-by-index expressions, which would be non-constant in the second example.
Random column references don’t workSo you’re thinking that you can just add a random column reference? Sometimes you can, but often you cannot:
The above examples show that you do not always have a column reference available in any given SQL expression. There is no useful column that you could refer to from the
-- This doesn't work: SELECT ROW_NUMBER() OVER ( ORDER BY [no-column-available-here] ) -- But this does! SELECT a FROM (VALUES (1), (2), (3), (4)) t(a) ORDER BY a OFFSET 3 ROWS
ROW_NUMBER()function. At the same time, you can write
ORDER BY ain the second example, but only if
ais a “comparable” value, i.e. not a LOB, such as
image. Besides, as we don’t really care about the actual ordering, is it worth ordering the result set by anything at all? Do you happen to have an index on
Quasi-constant ORDER BY expressions do workSo, to stay on the safe side, if ever you need a dummy
ORDER BYexpression in SQL Server, use a quasi-constant expression, like
@@language, or any of these). The following will always work:
From the upcoming jOOQ 3.4, we’ll also generate such synthetic
-- This always works: SELECT ROW_NUMBER() OVER (ORDER BY @@version) -- So does this: SELECT a FROM (VALUES (1), (2), (3), (4)) t(a) ORDER BY @@version OFFSET 3 ROWS
ORDER BYclauses that will help you simplify writing vendor-agnostic SQL in these edge-cases, as we believe that you simply shouldn’t think of these things all the time.
11 thoughts on “SQL Server Trick: Circumvent Missing ORDER BY Clause”
I only use a couple of database engines and it’s tricky already…It’s so hard to know all that stuff for all providers, so posting all these tips and tricks will make our lives so much easier, the next time we bump into any of these issues.
Nah, just use jOOQ and forget about these tips again ;-)
That’s a good point. My JPA to jOOQ mapping article is the most constantly daily accessed post. SQL has much to offer and jOOQ feels natural, especially after being so much habituated to JPA. I think the Hibernate “masters” will easily see all the advantages of jOOQ, and the rest will simply follow. What do you think, am I right on this one?
You could become an expert in that area! :-) But I do believe that you get some traction on that. We constantly promote it on social media, and we haven’t covered the subject much yet, ourselves.
If I had a little more time, I would delve into a CQRS architecture that combines JPA with jOOQ in a single application, where JPA does the Command (C) part and jOOQ does the Query (Q) part. Your post dealing with combined code generation is an awesome headstart.
An awesome integration would be to find a way how jOOQ’s
ConnectionProvider(or some other SPI) could be leveraged to redirect all jOOQ statements to an
createNativeQuery()API. Possibly, jOOQ-generated queries could directly bind variables on such a
javax.persistence.Queryinstead of a JDBC
PreparedStatement, sharing transaction managers and entity mapping algorithms, etc. What do you think?
From my experience, Java / Hibernate “masters” have yet to understand the power of SQL, which they have been trying to hide all along. This will all sink in slowly, but steadily. But even if SQL is great for some use-cases, it’s at best mediocre for others. So the ultimate combo (of my dreams, at least) would be a joint library: Hibernate + jOOQ. We’ll certainly be working towards such a goal…
You are right, JPA and jOOQ should have a binding mechanism and integrating it into EntityManager would simplify its usage, considering how many JPA applications are already running in production systems.
As for the JPA “masters”, if someone has taken the time and effort to understand Hibernate and its inner workings, he/she would realize its limitations and appreciate the value of jOOQ advanced querying mechanism.
I don’t think I ever met a JPA domain expert that wasn’t also an SQL aficionado, those things are inseparable anyway. Understanding means discovering strengths and weaknesses and JPA/ORM is no different.
The first time I browsed Martin Fowler’s EAA book, I was amazed how many of those patterns we now get for free by just using Hibernate: “Unit of Work”, “Optimistic locking”, “Dirty checking”, “Work flows”, “Long conversations”.
That’s the reason I think Hibernate is not an issue “per se”. The biggest problem is the lack of understanding combined with massive adoption, a recipe for failure. But this happened/happens with any other technologies as well: J2EE, NoSQL.
“Ditching JPA to start from scratch” reminds me of Joel Spolsky article about software rewrite (http://www.joelonsoftware.com/articles/fog0000000069.html):
“The idea that new code is better than old is patently absurd. Old code has been used. It has been tested. Lots of bugs have been found, and they’ve been fixed. There’s nothing wrong with it. It doesn’t acquire bugs just by sitting around on your hard drive. Au contraire, baby! Is software supposed to be like an old Dodge Dart, that rusts just sitting in the garage? Is software like a teddy bear that’s kind of gross if it’s not made out of all new material?”
Or Uncle Bob on Framework Bound (http://blog.8thlight.com/uncle-bob/2014/05/11/FrameworkBound.html):
“This shouldn’t be surprising. Frameworks are written by people to solve certain problems that they have. Those problems may be similar to yours, but they are not yours. You have different problems. To the extent that your problems overlap, the framework can be enormously helpful. To the extent that your problems conflict, the framework can be a huge impediment.”
Hibernate has been in use for over a decade, and from one project to the other it came to address many common problems, the ones we all face in many projects.
Getting to know JPA is not an easy ride, but is SQL any different? Or proper transaction management? All those take time and effort to properly master, and if one is not willing to invest, he won’t get any profit out of using them.
Hehe. Don’t get me wrong. I’m absolutely along the lines of your statement here:
No one said that JPA should be abandoned or re-written.
But I’m also pretty sure that “NamedEntityGraph” solves problems that only very few people have and than even less people should have. I’m very surprised, also, about the verbose implementation of stored procedure support in JPA 2.1 that provides only little improvement over stored procedure support in JDBC. About as much as I am still surprised about the Criteria API. If you have JPQL, why would you build a Criteria API that doesn’t really model JPQL?
So, I’m quite sure that JPA did take a couple of funny turns. You’ve listed that here:
Yes, of course it is different :-) You said it yourself. If you want to master JPA, you better master SQL as well. If you want to master SQL, you only need to master … SQL. Nevertheless, as I said, I agree with you that JPA solves many problems very well, and I most certainly agree with you that massive adoption has lead to many problems, as people took Hibernate / JPA for something that it isn’t. And that’s why we’re here. We provide that other feature set. The “R” in CQRS.
A CQRS example would be great blog post, the major challenge is to maintain the ACID properties between command and query. The pattern doesn’t imply an asynchronous event propagation and “eventual consistency” is a trade-off most application don’t really mandate.
I will have to evaluate the new JPA 2.1 changes myself. After “Criteria API”, I am sceptical about JPA new features.
True. I guess if you only use RDBMS-centric transactions (and no cache), you’ll be safe. Or so…
You are warned! :-) All I’m saying is: Annotatiomania™. And stored procedures.
I once saw an entity class where 90% of all code was taken by the “external” queries.
Wonderful ;-) Precisely my point…
There’s also this: https://gist.github.com/ronnieoverby/f4b17d0e7347104a4861