Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p><strong>Edited to include another version which i think is a bit more reliable, and also works with overlapping ranges</strong></p> <pre><code>CREATE TABLE #data (start_range INT, end_range INT) INSERT INTO #data VALUES (1,8) INSERT INTO #data VALUES (2,15) INSERT INTO #data VALUES (9,13) INSERT INTO #data VALUES (14,20) INSERT INTO #data VALUES (13,26) INSERT INTO #data VALUES (12,21) INSERT INTO #data VALUES (9,25) INSERT INTO #data VALUES (20,25) INSERT INTO #data VALUES (30,42) INSERT INTO #data VALUES (42,49) INSERT INTO #data VALUES (60,67) ;with ranges as ( SELECT start_range as level ,end_range as end_range ,row_number() OVER (PARTITION BY (SELECT NULL) ORDER BY start_range) as row FROM #data UNION ALL SELECT level + 1 as level ,end_range as end_range ,row From ranges WHERE level &lt; end_range ) ,ranges2 AS ( SELECT DISTINCT level FROM ranges ) ,ranges3 AS ( SELECT level ,row_number() OVER (ORDER BY level) - level as grouping_group from ranges2 ) SELECT MIN(level) as start_number ,MAX(level) as end_number FROM ranges3 GROUP BY grouping_group ORDER BY start_number ASC </code></pre> <hr> <p>I think this should work - might not be especially efficient on larger sets though...</p> <pre><code>CREATE TABLE #data (start_range INT, end_range INT) INSERT INTO #data VALUES (1,8) INSERT INTO #data VALUES (2,15) INSERT INTO #data VALUES (9,13) INSERT INTO #data VALUES (14,20) INSERT INTO #data VALUES (21,25) INSERT INTO #data VALUES (30,42) INSERT INTO #data VALUES (42,49) INSERT INTO #data VALUES (60,67) ;with overlaps as ( select * ,end_range - start_range as range ,row_number() OVER (PARTITION BY (SELECT NULL) ORDER BY start_range ASC) as line_number from #data ) ,overlaps2 AS ( SELECT O1.start_range ,O1.end_range ,O1.line_number ,O1.range ,O2.start_range as next_range ,CASE WHEN O2.start_range - O1.end_range &lt; 2 THEN 1 ELSE 0 END as overlap ,O1.line_number - DENSE_RANK() OVER (PARTITION BY (CASE WHEN O2.start_range - O1.end_range &lt; 2 THEN 1 ELSE 0 END) ORDER BY O1.line_number ASC) as overlap_group FROM overlaps O1 LEFT OUTER JOIN overlaps O2 on O2.line_number = O1.line_number + 1 ) SELECT MIN(start_range) as range_start ,MAX(end_range) as range_end ,MAX(end_range) - MIN(start_range) as range_span FROM overlaps2 GROUP BY overlap_group </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POGroup close numbers
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USDibstar
    1. USDibstar
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO+1 Tested here and it worked. Good thing you included the `CREATE` and the `INSERT` statements.
      singulars
      1. PO
      1. USAdrian Carneiro
    2. COHi Davin, Your second solution is much more reliable since the first one didn't worked well. Actually, the original tables don't contains any overlaps. If you have any idea how to resole the problem without overlaps in more efficient way I would like to know. Tx for your help :)
      singulars
      1. PO
      1. USLiran Ben Yehuda
    3. CO@Liran Ben Yehuda - in your original question you wanted examples of 1-10,11-20 AND 1-10,10-20 to give a range of 1-20 - so there is overlap as in the second case 10 appears twice, does this mean then that in your actual tables each start and end range value is unique?
      singulars
      1. PO
      1. USDibstar
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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