New answers tagged

0

This is due to relation attributes (defined in pg_class and pg_attribute, or defined dynamically from a select statement) supporting modifiers (via pg_attribute.atttypmod), whilst function parameters do not. Modifiers are lost when processed through functions, and since all operators are handled via functions, modifiers are lost when processed by operators ...


2

Normally you can utilize windowed aggregate functions when you need to access the previous row's data, but here the current row's calculation is based on the previous result, not row. For this a kind of question I never found a set based solution with acceptable performance. But it's easy to write using row-by-row logic. I prefer materializing a ROW_NUMBER ...


2

This was hard! I don't know if this is simpler, but at least it doesn't use window function nor produce rows that require being filtered out. with recursive r(k, n) as ( with t(k) as (values (1),(2),(3),(4),(5)) -- the data we want to filter -- with t(k) as (values (1),(5),(7),(10),(11),(12),(13)) -- with t(k) as (values ...


7

Anecdotally, I prefer to name the columns inside the CTE instead of inside the WITH CTE (xxx) AS1 clause since you'll never inadvertently mismatch the names vs the column contents. Take for instance the following example: ;WITH MyCTE (x, y) AS ( SELECT mt.y , mt.x FROM MySchema.MyTable mt ) SELECT MyCTE.x , MyCTE.y FROM MyCTE; What ...


5

Ultimately each column needs a valid name and you can assign it in two ways: Column list ;WITH cte (foo) AS ( select col from tab ) select foo from cte; Using original column names or aliases ;WITH cte AS ( select col from tab ) select col from cte; When you do both alias and column list Both column list & aliases ;WITH cte (foo, bar) AS ( ...


18

You nearly have the answer for one of your questions already. In the MSDN page, there is a line directly after your quote that explains this: The basic syntax structure for a CTE is: WITH expression_name [ ( column_name [,...n] ) ] AS ( CTE_query_definition ) The list of column names is optional only if distinct names for all ...


0

this is my solution. As I mentioned in a previous comment, it's not the prettiest solution stylistically, but it works. DECLARE @Id int = 1092 DECLARE @i INT = 0, @IdToProcess INT = 0 DECLARE @t TABLE ( Id INT, Processed BIT ) INSERT INTO @t (Id, Processed) VALUES (@Id, 0) WHILE ...



Top 50 recent answers are included