Gaps and islands problems involve finding a range of missing values (gaps) or a range of consecutive values (islands) in a sequence of numbers or dates.
6
votes
4answers
286 views
Gaps and islands: client solution vs T-SQL query
Can a T-SQL solution for gaps and islands run faster than a C# solution running on the client?
To be specific, let us provide some test data:
CREATE TABLE dbo.Numbers
(
n INT NOT NULL
...
6
votes
6answers
394 views
Find “n” consecutive free numbers from table
I have some table with numbers like this (status is either FREE or ASSIGNED)
id_set number status
-----------------------
1 000001 ASSIGNED
1 000002 FREE
1 000003 ...
4
votes
2answers
115 views
Select longest continuous sequence
I am trying to construct a query in PostgreSQL 9.0 that gets the longest sequence of continuous rows for a specifc field.
Consider the following table:
lap_id (Serial), lap_no (int), car_type ...
2
votes
1answer
246 views
Find which numbers in [1, 161] are not in the result set?
How to find which two number in [1, 161] is not in the result set?
Can I find this two numbers using sql command?
mysql> select blog_id from wp_blogs;
...
| 149 |
| 150 |
| 151 |
| ...
3
votes
1answer
328 views
Stored procedure to check for missing fields in a table
I have a stored procedure that accepts a string parameter, a comma separated list of required column names that the SP should check a particular table and make sure those columns have data in them... ...
5
votes
2answers
241 views
Performance tuning for gap analysis of table
I have a table which stores the sequence (counter) of data received from devices out in the field. At any rate, these sequences need to be in order within a configurable time span, but can come into ...
1
vote
3answers
256 views
Find and remove gaps in sequence across two different columns
I've tried to solve this problem using a set-based approach. However, since I need to look at each row I think I must use a cursor; please correct me if I'm wrong.
The table:
Project, item, method, ...
7
votes
3answers
855 views
Using Row_Number to find consecutive row count
I have this column of ints that represent an occurrence of a signal and I'm trying to add a column that shows the count of consecutive row
If my data looks like this
724
727
728
733
735
737
743
747
...
4
votes
2answers
576 views
Updating rows conditionally with a gapless ascending value
A user has a table they would like to update. Depending on the data in other columns they want a particular column to be updated with an ascending value starting from 1 that is gap-less. The rows ...
