Aggregate numbers that are in sequence
I have a table part_tab with column serial_no:
PART_NO SERIAL_NO
A 1
A 2
A 3
A 5
A 7
A 8
A 9
A 10
I would like to aggregate serial_no values in one row when they are in sequence:
PART_NO SERIAL_NO
A 1-3
A 5
A 7-10
Grouping is by part_no. So for Part No "A" I would like to select the serial_no in one column with values "1-3", "5", "7-10". Selected column should range from min to max and in increasing order.
oracle plsql gaps-and-islands
edited Nov 13 '18 at 15:36
trincot
122k1586119
asked Nov 13 '18 at 14:50
slayer22slayer22
95
add a comment |
I have a table part_tab with column serial_no:
PART_NO SERIAL_NO
A 1
A 2
A 3
A 5
A 7
A 8
A 9
A 10
I would like to aggregate serial_no values in one row when they are in sequence:
PART_NO SERIAL_NO
A 1-3
A 5
A 7-10
Grouping is by part_no. So for Part No "A" I would like to select the serial_no in one column with values "1-3", "5", "7-10". Selected column should range from min to max and in increasing order.
oracle plsql gaps-and-islands
edited Nov 13 '18 at 15:36
trincot
122k1586119
asked Nov 13 '18 at 14:50
slayer22slayer22
95
You should explain the logic for grouping also...
– trincot
Nov 13 '18 at 14:56
1
Why do you think you need a stored procedure?
– a_horse_with_no_name
Nov 13 '18 at 15:03
It can be procedure...
– slayer22
Nov 13 '18 at 15:10
I edited your question in order to better describe the output. Could you check the modification is in line with your expectations?
– trincot
Nov 13 '18 at 15:40
Thx trincot your title is beter than mine
– slayer22
Nov 14 '18 at 13:53
add a comment |
I have a table part_tab with column serial_no:
PART_NO SERIAL_NO
A 1
A 2
A 3
A 5
A 7
A 8
A 9
A 10
I would like to aggregate serial_no values in one row when they are in sequence:
PART_NO SERIAL_NO
A 1-3
A 5
A 7-10
Grouping is by part_no. So for Part No "A" I would like to select the serial_no in one column with values "1-3", "5", "7-10". Selected column should range from min to max and in increasing order.
oracle plsql gaps-and-islands
edited Nov 13 '18 at 15:36
trincot
122k1586119
asked Nov 13 '18 at 14:50
slayer22slayer22
95
I have a table part_tab with column serial_no:
PART_NO SERIAL_NO
A 1
A 2
A 3
A 5
A 7
A 8
A 9
A 10
I would like to aggregate serial_no values in one row when they are in sequence:
PART_NO SERIAL_NO
A 1-3
A 5
A 7-10
Grouping is by part_no. So for Part No "A" I would like to select the serial_no in one column with values "1-3", "5", "7-10". Selected column should range from min to max and in increasing order.
oracle plsql gaps-and-islands
oracle plsql gaps-and-islands
edited Nov 13 '18 at 15:36
trincot
122k1586119
asked Nov 13 '18 at 14:50
slayer22slayer22
95
edited Nov 13 '18 at 15:36
trincot
122k1586119
asked Nov 13 '18 at 14:50
slayer22slayer22
95
edited Nov 13 '18 at 15:36
trincot
122k1586119
edited Nov 13 '18 at 15:36
trincot
122k1586119
edited Nov 13 '18 at 15:36
trincot
122k1586119
122k1586119
asked Nov 13 '18 at 14:50
slayer22slayer22
95
asked Nov 13 '18 at 14:50
slayer22slayer22
95
asked Nov 13 '18 at 14:50
slayer22slayer22
95
95
You should explain the logic for grouping also...
– trincot
Nov 13 '18 at 14:56
1
Why do you think you need a stored procedure?
– a_horse_with_no_name
Nov 13 '18 at 15:03
It can be procedure...
– slayer22
Nov 13 '18 at 15:10
I edited your question in order to better describe the output. Could you check the modification is in line with your expectations?
– trincot
Nov 13 '18 at 15:40
Thx trincot your title is beter than mine
– slayer22
Nov 14 '18 at 13:53
add a comment |
You should explain the logic for grouping also...
– trincot
Nov 13 '18 at 14:56
1
Why do you think you need a stored procedure?
– a_horse_with_no_name
Nov 13 '18 at 15:03
It can be procedure...
– slayer22
Nov 13 '18 at 15:10
I edited your question in order to better describe the output. Could you check the modification is in line with your expectations?
– trincot
Nov 13 '18 at 15:40
Thx trincot your title is beter than mine
– slayer22
Nov 14 '18 at 13:53
You should explain the logic for grouping also...
– trincot
Nov 13 '18 at 14:56
You should explain the logic for grouping also...
– trincot
Nov 13 '18 at 14:56
1
1
Why do you think you need a stored procedure?
– a_horse_with_no_name
Nov 13 '18 at 15:03
Why do you think you need a stored procedure?
– a_horse_with_no_name
Nov 13 '18 at 15:03
It can be procedure...
– slayer22
Nov 13 '18 at 15:10
It can be procedure...
– slayer22
Nov 13 '18 at 15:10
I edited your question in order to better describe the output. Could you check the modification is in line with your expectations?
– trincot
Nov 13 '18 at 15:40
I edited your question in order to better describe the output. Could you check the modification is in line with your expectations?
– trincot
Nov 13 '18 at 15:40
Thx trincot your title is beter than mine
– slayer22
Nov 14 '18 at 13:53
Thx trincot your title is beter than mine
– slayer22
Nov 14 '18 at 13:53
add a comment |
2 Answers
2
active
oldest
votes
You could do it without pl/sql, using a query with some common table expressions (with clause). It would look like this:
with add_break as (
select part_no,
serial_no,
serial_no-1-lag(serial_no,1,0) over (partition by part_no order by serial_no) brk
from part_tab
),
add_group as (
select add_break.*,
sum(brk) over (partition by part_no order by serial_no) as grp
from add_break
)
select part_no,
case when min(serial_no) = max(serial_no) then to_char(min(serial_no))
else to_char(min(serial_no)) || '-' || to_char(max(serial_no))
end range
from add_group
group by part_no, grp
order by 1, 2
Output for your example data:
part_no | range
--------+------
A | 1-3
A | 5
A | 7-10
answered Nov 13 '18 at 15:29
trincottrincot
122k1586119
Thank you! This is exactly what I mean
– slayer22
Nov 14 '18 at 13:55
add a comment |
That is a gaps & islands problem you can approach by numbering your rows and subtracting those numbers from the serial numbers. This gives you the groups you need.
select
part_no,
case when min(serial_no) = max(serial_no)
then to_char(min(serial_no))
else min(serial_no) || '-' || max(serial_no)
end as serial_nos
from
(
select
part_no,
serial_no,
serial_no - row_number() over (partition by part_no order by serial_no) as grp
from mytable
)
group by part_no, grp
order by part_no, min(serial_no);
answered Nov 13 '18 at 15:50
Thorsten KettnerThorsten Kettner
51.5k32642
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53283634%2faggregate-numbers-that-are-in-sequence%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You could do it without pl/sql, using a query with some common table expressions (with clause). It would look like this:
with add_break as (
select part_no,
serial_no,
serial_no-1-lag(serial_no,1,0) over (partition by part_no order by serial_no) brk
from part_tab
),
add_group as (
select add_break.*,
sum(brk) over (partition by part_no order by serial_no) as grp
from add_break
)
select part_no,
case when min(serial_no) = max(serial_no) then to_char(min(serial_no))
else to_char(min(serial_no)) || '-' || to_char(max(serial_no))
end range
from add_group
group by part_no, grp
order by 1, 2
Output for your example data:
part_no | range
--------+------
A | 1-3
A | 5
A | 7-10
answered Nov 13 '18 at 15:29
trincottrincot
122k1586119
Thank you! This is exactly what I mean
– slayer22
Nov 14 '18 at 13:55
add a comment |
You could do it without pl/sql, using a query with some common table expressions (with clause). It would look like this:
with add_break as (
select part_no,
serial_no,
serial_no-1-lag(serial_no,1,0) over (partition by part_no order by serial_no) brk
from part_tab
),
add_group as (
select add_break.*,
sum(brk) over (partition by part_no order by serial_no) as grp
from add_break
)
select part_no,
case when min(serial_no) = max(serial_no) then to_char(min(serial_no))
else to_char(min(serial_no)) || '-' || to_char(max(serial_no))
end range
from add_group
group by part_no, grp
order by 1, 2
Output for your example data:
part_no | range
--------+------
A | 1-3
A | 5
A | 7-10
answered Nov 13 '18 at 15:29
trincottrincot
122k1586119
Thank you! This is exactly what I mean
– slayer22
Nov 14 '18 at 13:55
add a comment |
You could do it without pl/sql, using a query with some common table expressions (with clause). It would look like this:
with add_break as (
select part_no,
serial_no,
serial_no-1-lag(serial_no,1,0) over (partition by part_no order by serial_no) brk
from part_tab
),
add_group as (
select add_break.*,
sum(brk) over (partition by part_no order by serial_no) as grp
from add_break
)
select part_no,
case when min(serial_no) = max(serial_no) then to_char(min(serial_no))
else to_char(min(serial_no)) || '-' || to_char(max(serial_no))
end range
from add_group
group by part_no, grp
order by 1, 2
Output for your example data:
part_no | range
--------+------
A | 1-3
A | 5
A | 7-10
answered Nov 13 '18 at 15:29
trincottrincot
122k1586119
You could do it without pl/sql, using a query with some common table expressions (with clause). It would look like this:
with add_break as (
select part_no,
serial_no,
serial_no-1-lag(serial_no,1,0) over (partition by part_no order by serial_no) brk
from part_tab
),
add_group as (
select add_break.*,
sum(brk) over (partition by part_no order by serial_no) as grp
from add_break
)
select part_no,
case when min(serial_no) = max(serial_no) then to_char(min(serial_no))
else to_char(min(serial_no)) || '-' || to_char(max(serial_no))
end range
from add_group
group by part_no, grp
order by 1, 2
Output for your example data:
part_no | range
--------+------
A | 1-3
A | 5
A | 7-10
answered Nov 13 '18 at 15:29
trincottrincot
122k1586119
edited Nov 14 '18 at 14:13
answered Nov 13 '18 at 15:29
trincottrincot
122k1586119
answered Nov 13 '18 at 15:29
trincottrincot
122k1586119
answered Nov 13 '18 at 15:29
trincottrincot
122k1586119
122k1586119
Thank you! This is exactly what I mean
– slayer22
Nov 14 '18 at 13:55
add a comment |
Thank you! This is exactly what I mean
– slayer22
Nov 14 '18 at 13:55
Thank you! This is exactly what I mean
– slayer22
Nov 14 '18 at 13:55
Thank you! This is exactly what I mean
– slayer22
Nov 14 '18 at 13:55
add a comment |
That is a gaps & islands problem you can approach by numbering your rows and subtracting those numbers from the serial numbers. This gives you the groups you need.
select
part_no,
case when min(serial_no) = max(serial_no)
then to_char(min(serial_no))
else min(serial_no) || '-' || max(serial_no)
end as serial_nos
from
(
select
part_no,
serial_no,
serial_no - row_number() over (partition by part_no order by serial_no) as grp
from mytable
)
group by part_no, grp
order by part_no, min(serial_no);
answered Nov 13 '18 at 15:50
Thorsten KettnerThorsten Kettner
51.5k32642
add a comment |
That is a gaps & islands problem you can approach by numbering your rows and subtracting those numbers from the serial numbers. This gives you the groups you need.
select
part_no,
case when min(serial_no) = max(serial_no)
then to_char(min(serial_no))
else min(serial_no) || '-' || max(serial_no)
end as serial_nos
from
(
select
part_no,
serial_no,
serial_no - row_number() over (partition by part_no order by serial_no) as grp
from mytable
)
group by part_no, grp
order by part_no, min(serial_no);
answered Nov 13 '18 at 15:50
Thorsten KettnerThorsten Kettner
51.5k32642
add a comment |
That is a gaps & islands problem you can approach by numbering your rows and subtracting those numbers from the serial numbers. This gives you the groups you need.
select
part_no,
case when min(serial_no) = max(serial_no)
then to_char(min(serial_no))
else min(serial_no) || '-' || max(serial_no)
end as serial_nos
from
(
select
part_no,
serial_no,
serial_no - row_number() over (partition by part_no order by serial_no) as grp
from mytable
)
group by part_no, grp
order by part_no, min(serial_no);
answered Nov 13 '18 at 15:50
Thorsten KettnerThorsten Kettner
51.5k32642
That is a gaps & islands problem you can approach by numbering your rows and subtracting those numbers from the serial numbers. This gives you the groups you need.
select
part_no,
case when min(serial_no) = max(serial_no)
then to_char(min(serial_no))
else min(serial_no) || '-' || max(serial_no)
end as serial_nos
from
(
select
part_no,
serial_no,
serial_no - row_number() over (partition by part_no order by serial_no) as grp
from mytable
)
group by part_no, grp
order by part_no, min(serial_no);
answered Nov 13 '18 at 15:50
Thorsten KettnerThorsten Kettner
51.5k32642
answered Nov 13 '18 at 15:50
Thorsten KettnerThorsten Kettner
51.5k32642
answered Nov 13 '18 at 15:50
Thorsten KettnerThorsten Kettner
51.5k32642
answered Nov 13 '18 at 15:50
Thorsten KettnerThorsten Kettner
51.5k32642
51.5k32642
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53283634%2faggregate-numbers-that-are-in-sequence%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
You should explain the logic for grouping also...
– trincot
Nov 13 '18 at 14:56
1
Why do you think you need a stored procedure?
– a_horse_with_no_name
Nov 13 '18 at 15:03
It can be procedure...
– slayer22
Nov 13 '18 at 15:10
I edited your question in order to better describe the output. Could you check the modification is in line with your expectations?
– trincot
Nov 13 '18 at 15:40
Thx trincot your title is beter than mine
– slayer22
Nov 14 '18 at 13:53