Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>As gelonsoft noted, there are many ways to do this. I'd prefer to only hit the table once though. This is my current favourite, based on a method I first found on this site, probably on <a href="https://stackoverflow.com/a/4324654/266304">this answer</a> (and you might find many more approaches <a href="https://stackoverflow.com/tags/gaps-and-islands/hot">among these questions</a>:</p> <pre><code>select inventory_group_id, code, start_num, case when end_num = start_num then null else end_num end as end_num from ( select inventory_group_id, code, min(num) as start_num, max(num) as end_num from ( select inventory_group_id, num, code, row_number() over (order by num) - row_number() over (partition by code order by num) as chain from inventory_num ) group by inventory_group_id, code, chain ) order by 1,3; INVENTORY_GROUP_ID C START_NUM END_NUM ------------------ - ---------- ---------- 100003894 E 211 213 100003894 I 214 100003894 E 515 519 100003894 C 520 521 100003894 E 522 100003894 I 523 100003894 E 524 527 </code></pre> <p>The inner select is doing all the work, by creating artificial groupings based on the <code>code</code> and <code>num</code> values - run that on its own to see what it's doing. The next query out is collapsing the groups to find the lowest and highest <code>num</code> for each of the artificial groups. The final outer query is purely to make the <code>end</code> value <code>null</code> if the chain only has one link - i.e. the <code>start</code> and <code>end</code> are the same - which you mentioned you wanted.</p> <p>What you haven't said is whether the <code>num</code> values have to be contiguous. If I add a record with <code>code='I'</code> and <code>num=216</code>, I get the same number of outputs, but <code>214-216</code> is treated as one chain, even though there is no <code>215</code> value in between:</p> <pre><code>INVENTORY_GROUP_ID C START_NUM END_NUM ------------------ - ---------- ---------- 100003894 E 211 213 100003894 I 214 216 ... </code></pre> <p>I haven't figured out how to adapt this <code>row_number()</code> method to take account of that and treat them as separate chains - I'd be interested to see if it can be done while keeping it simple. The same thing happens with the other answers given; but I'm not sure if it matters to you.</p> <p>If it does, here's another version which only hits the table once; rather more convoluted and in this form has the same potential problem with non-contiguous <code>num</code> values:</p> <pre><code>select distinct inventory_group_id, code, case when prev_code = code then lag(num) over (order by rn) else num end as start_num, case when next_code != code and prev_code != code then null when next_code = code then lead(num) over (order by rn) else num end as end_num from ( select inventory_group_id, num, code, prev_code, next_code, rownum as rn from ( select inventory_group_id, num, code, lag(code) over (partition by inventory_group_id order by num) as prev_code, lead(code) over (partition by inventory_group_id order by num) as next_code from inventory_num ) where (prev_code is null or code != prev_code) or (next_code is null or code != next_code) order by 1,2,3 ) order by 1,3,2; INVENTORY_GROUP_ID C START_NUM END_NUM ------------------ - ---------- ---------- 100003894 E 211 213 100003894 I 214 100003894 E 515 519 100003894 C 520 521 100003894 E 522 100003894 I 523 100003894 E 524 527 </code></pre> <p>The inner query selects from the table, and uses the <code>lead</code> and <code>lag</code> <a href="http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions004.htm" rel="nofollow noreferrer">analytic functions</a> to find the code either side of each row.</p> <p>The next query out excludes any rows that have the <em>same</em> code as both the next and previous rows; that is, anything that's in the middle of an uninterrupted chain. That means each chain is collapsed to at most two rows, with the start and end <code>num</code> values for that chain.</p> <p>The <code>case</code> statement in the outer query make both rows for each chain look the same, by using <code>lead</code> and <code>lag</code> again; if there is only one row (e.g. for <code>214</code>) the first <code>when</code> clause of the second <code>case</code> makes the end value <code>null</code>, which you said you wanted. The <code>distinct</code> then removes the duplicates the <code>case</code> created.</p> <p>I suggest you run each level of the query separately to see what it's doing, and understand what it's doing to the previous one.</p> <p>As I said, this has the same potential problem if I introduce a row with <code>code='I'</code> and <code>num=216</code>:</p> <pre><code>INVENTORY_GROUP_ID C START_NUM END_NUM ------------------ - ---------- ---------- 100003894 E 211 213 100003894 I 214 216 ... </code></pre> <p>This can be split into two chains by adapting this method, but it's a little more complicated as you have to track and compare the <code>num</code> values as well as the <code>code</code> values:</p> <pre><code>select distinct inventory_group_id, code, case when prev_num is null then num when prev_code = code then lag(num) over (order by rn) else num end as start_num, case when next_code != code and prev_code != code then null when next_code is null then num when next_num is null then null when next_code = code then lead(num) over (order by rn) else num end as end_num from ( select inventory_group_id, num, code, prev_code, next_code, case when prev_num != num - 1 then null else prev_num end as prev_num, case when next_num != num + 1 then null else next_num end as next_num, rownum as rn from ( select inventory_group_id, num, code, lag(code) over (partition by inventory_group_id order by num) as prev_code, lead(code) over (partition by inventory_group_id order by num) as next_code, lag(num) over (partition by inventory_group_id order by num) as prev_num, lead(num) over (partition by inventory_group_id order by num) as next_num from inventory_num ) where (prev_code is null or code != prev_code) or (next_code is null or code != next_code) or (prev_num is null or num != prev_num + 1) or (next_num is null or num != next_num - 1) order by 1,2,3 ) order by 1,3,2; INVENTORY_GROUP_ID C START_NUM END_NUM ------------------ - ---------- ---------- 100003894 E 211 213 100003894 I 214 100003894 I 216 100003894 E 515 519 100003894 C 520 521 100003894 E 522 100003894 I 523 100003894 E 524 527 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POOracle SQL. What statement should I use
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USAlex Poole
    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. This table or related slice is empty.
    1. This table or related slice is empty.
 

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