473,320 Members | 1,916 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Select a group of numbers in sequence

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.

1 2024
Rabbit
12,516 Expert Mod 8TB
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

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

Similar topics

3
by: Disco | last post by:
Hi, I would like to be able to do something like this... select name,dob from employees where > 10 and < 20; .... results from this would show me data from the 10th, 11th,... and 19th...
0
by: cooldv | last post by:
i have an *access database + ASP newsletter* that is working fine and it sends newsletter to ALL the email addresses in the DB. i want to send this newsletter to only a select group (like people...
4
by: Mike Nolan | last post by:
I'm trying to create a summary log by hour. Here's the query (somewhat simplified): select to_char(mtrantime,'mm-dd hh AM') as datetime, count(*) as tot from memtran group by datetime order...
5
by: jacob.dba | last post by:
I have a table with first name, last name, SSN(social security number) and other columns. I want to assign group number according to this business logic. 1. Records with equal SSN and (similar...
10
by: Chris | last post by:
I am using an access database and have range of years for customers and want to group them together. for example: 1990 1991 1992 1993 1994 1998
1
by: ywangmda | last post by:
Hi everyone! I am a new user of Crystal Reports and have a question on Crystal Reports 10. I have a set of data with values such as 1.01 to 400.99, and would like to group them in every 1, that is,...
2
by: gimme_this_gimme_that | last post by:
The following statement doesn't work and I need a tip. Here is a draft: This selects data from the z table. And fetches the next value for r_id_seq and creates a table named b. select...
2
by: pedalpete | last post by:
I seem to have a few duplicates entries in a very large database. My database has two identical tables. One is written to on a regular basis (every few seconds) and the other is used for queries,...
2
by: RolfK | last post by:
Dear ALL, I need some help on xsl:sequence. I'm using the Altova XSLT processor, but I'm quite confident it is not an processor issue. It's probably my bad knowledge of xslt 2.0. I have probably...
1
by: Al Holliday | last post by:
I have the following SELECT statement SELECT MU.SUBID, MU.LOCID, MU.MENUCAT, MU.MENUNAME, MU.RECIPENAME, ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.