Databases - TopAnswers

CONNECT BY Hierarchical Queries

Concept

Community Community

0 warning As the documentation says, ORDER BY or GROUP BY destroy the depth-first hierarchical ordering of the results:

Jack Douglas

0 example A simple example of `LEVEL` and `SYS_CONNECT_BY_PATH`:

Jack Douglas

0 example There is a trick to get any specified number of rows from `dual`:

Jack Douglas

edit the code so that it calculates post reference year as well.

netezza

Anonymous Anonymous

Reverse string by parts with strict ordering

postgresql

PeterVandivier PeterVandivier

2 If you want to guarantee ordering (and you should), you can use [with ordinality](https://www.postgresql.org/docs/12/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS) since version 9.4, along with an aggregate with [order by](https://www.postgresql.org/docs/12/sql-expressions.html#SYNTAX-AGGREGATES):

Jack Douglas

Why Does One Constraint Prevent A Sort?

sql-server

Erik Darling Erik Darling

8 Essentially it's the same reason

Paul White

Too many VLFs - How do I truncate them?

sql-server

karun_r karun_r

2 > Too many VLFs - How do I truncate them?

James Jenkins

Should we give stars for questions? 3

Meta Poll

i-one i-one

15 No, an answer is the best reward for a question.

Community

3 Yes, but stars awarded are deducted from your own stars (like a mini-bounty)

Community

2 Yes, but stars are only allowed from people who can multi-star (they can still award any number of stars).

Community

2 Yes, as much stars as user is allowed to give.

Community

1 Yes, one star at most.

Community

Immutable pure-SQL timestamp->epoch conversion

postgresql-12

PeterVandivier PeterVandivier

7 Yes, you can: just convert to `timestamp` at UTC. This is not what you might expect.

Jack Douglas

Why does adding a TOP allow the SQL Server query optimizer to find a plan that can honor a FORCESEEK hint?

sql-server

Joe Obbish Joe Obbish

13 Although the query text contains an `APPLY`, the optimizer rewrites this to a regular join using the `ApplyHandler` rule early in the compilation process (during simplification). The optimizer prefers joins over applies because it has more transformations for joins[^1](For background on the difference between join and apply, see my article [Apply versus Nested Loops Join](https://www.sql.kiwi/2019/06/apply-versus-nested-loops-join.html)).

Paul White

8 To supplement Paul's answer, ironically, the optimizer is capable of producing apply plan for the original query, if `OUTER APPLY` is used instead of the `CROSS APPLY`

i-one

db<>fiddle Feedback 5

Meta

dbfiddle

Jack Douglas Jack Douglas

5 The "ff" ligature destroys the cursor position. See GIF below:

fiatjaf

4 `bug` `fixed` Brittle fiddle one-boxing in chat

PeterVandivier

4 `bug` `fixed` YYYY-MM-DD for SQL Server

PeterVandivier

4 `bug` `fixed` Embedded fiddles should respect `&hide=` args

PeterVandivier

4 I have several links to db<>fiddle for demos in the following post:

Solomon Rutzky

4 `bug` clicking 'Back' should switch back not only the query, but also the database selection.

Ami

3 `feature-request` please add an (opt-in) cookie to remember the last platform a user was on?

PeterVandivier

2 `bug` All-null rows collapsing in embedded fiddles

PeterVandivier

2 `feature-request` please add CockroachDB

PeterVandivier

2 `feature-request` `declined` add an 'Update this post' button on embedded db<>fiddles on TopAnswers

PeterVandivier

2 `bug` `fixed`: Firebird, datatypes, aligning

Arioch

0 `feature-request` `retracted` Add support for SQL Server `GO [count]` syntax

John aka hot2use

0 `feature-request` [`declined`](https://topanswers.xyz/transcript?room=37&id=17302#c17302) Allow multiple statements per batch for Firebird again

Arioch