By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,964 Members | 1,290 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,964 IT Pros & Developers. It's quick & easy.

Select a group of numbers in sequence

P: 2
I have a Postgresql table with the following structure:

id | value
----+--------
1 | 1
2 | 2
3 | 3
4 | 4
5 | 1
6 | 2
7 | 3
8 | 1
9 | 2
10 | 3
11 | 4
12 | 5

I need to group these rows by (value) numbers in sequence. So I need to query the table and return
rows with ids 1-4 in one group, rows with ids 5 -7 in another group, and rows with ids 8-12 in another group.
Sep 26 '14 #1

✓ answered by Rabbit

Left join the table to itself on the current id to the next id. Keep the one where the value in the second instance is a 1 or is a null. That will give you just the last number in each sequence. Then you just need to create a calculated column that appends the string '1-' before the current value.

Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,347
Left join the table to itself on the current id to the next id. Keep the one where the value in the second instance is a 1 or is a null. That will give you just the last number in each sequence. Then you just need to create a calculated column that appends the string '1-' before the current value.
Sep 26 '14 #2

Post your reply

Sign in to post your reply or Sign up for a free account.