LAG window function looks at some row behind. It can be emulated with the ROWS BETWEEN clause. We do it here and then compare it with the direct call. Finally, we show you how LAG works with a visual image of the partition used by both LEAD/LAG and the ROWS-based window functions.
We emulate the SQL Lead window function with the ROWS BETWEEN clause, and compare it with a direct call. We show you a visual image of the partition on which both LEAD/LAG and ROWS-based window functions work.
There is no PIVOT operation in MySQL, but you can write tedious SQL queries to generate cross-tables. Use just a fraction of your time instead, and visually design complex PIVOT queries for MySQL with our Data Xtractor. You do not even need to know SQL, as our queries will be automatically generated for you.
We do not use BETWEEN for now, and chances are we’ll never expose it as a direct operator in our query builder. Sure, there are pros and cons on using x BETWEEN a AND b rather then x >= a AND x <= b, and that’s one reason why we have this article. However, assuming our tool tries to avoids as much as possible error prone SQL constructs and the “element of surprise”, it seems like the bad won over the good. (more…)
Query Xtractor is mainly a productivity tool. But it can be also used to learn SQL. One particularly great way to do it is to turn ON one of the Always Emulate options. We have this for grouping sets, crosstabs, Intersect/Except, All/Some/Any. With the setting OFF, we use the native support when available, and we emulate features when there is no other way. The obvious advantage in having ALL/SOME/ANY expressions emulated as well for let’s say SQL Server – where they are recognized and implemented by the vendor – is to better understand what they actually do through equivalent constructs. (more…)