StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Self-joins.</p> <p>For the table:</p> <pre><code>/* CREATE TABLE [dbo].[stackoverflow_203302]( [val] [int] NOT NULL ) ON [PRIMARY] */ </code></pre> <p>With parameter <code>@val</code></p> <pre><code>SELECT cur.val, MAX(prv.val) AS prv_val, MIN(nxt.val) AS nxt_val FROM stackoverflow_203302 AS cur LEFT JOIN stackoverflow_203302 AS prv ON cur.val > prv.val LEFT JOIN stackoverflow_203302 AS nxt ON cur.val < nxt.val WHERE cur.val = @val GROUP BY cur.val </code></pre> <p>You could make this a stored procedure with output parameters or just join this as a correlated subquery to the data you are pulling.</p> <p>Without the parameter, for your data the result would be:</p> <pre><code>val prv_val nxt_val ----------- ----------- ----------- 1 NULL 3 3 1 8 8 3 19 19 8 45 45 19 67 67 45 NULL </code></pre> <p>For the modified example, you use this as a correlated subquery:</p> <pre><code>/* CREATE TABLE [dbo].[stackoverflow_203302]( [ky] [int] NOT NULL, [val] [int] NOT NULL, CONSTRAINT [PK_stackoverflow_203302] PRIMARY KEY CLUSTERED ( [ky] ASC ) ) */ SELECT cur.ky AS cur_ky ,cur.val AS cur_val ,prv.ky AS prv_ky ,prv.val AS prv_val ,nxt.ky AS nxt_ky ,nxt.val as nxt_val FROM ( SELECT cur.ky, MAX(prv.ky) AS prv_ky, MIN(nxt.ky) AS nxt_ky FROM stackoverflow_203302 AS cur LEFT JOIN stackoverflow_203302 AS prv ON cur.ky > prv.ky LEFT JOIN stackoverflow_203302 AS nxt ON cur.ky < nxt.ky GROUP BY cur.ky ) AS ordering INNER JOIN stackoverflow_203302 as cur ON cur.ky = ordering.ky LEFT JOIN stackoverflow_203302 as prv ON prv.ky = ordering.prv_ky LEFT JOIN stackoverflow_203302 as nxt ON nxt.ky = ordering.nxt_ky </code></pre> <p>With the output as expected:</p> <pre><code>cur_ky cur_val prv_ky prv_val nxt_ky nxt_val ----------- ----------- ----------- ----------- ----------- ----------- 1 1 NULL NULL 2 3 2 3 1 1 3 8 3 8 2 3 4 19 4 19 3 8 5 67 5 67 4 19 6 45 6 45 5 67 NULL NULL </code></pre> <p>In SQL Server, I prefer to make the subquery a Common table Expression. This makes the code seem more linear, less nested and easier to follow if there are a lot of nestings (also, less repetition is required on some re-joins).</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload