Tag Info

Hot answers tagged

14

The general name for this type of query is "gaps and islands". One approach below. If you can have duplicates in the source data you might need dense_rank rather than row_number WITH DATA(C) AS ( SELECT 724 UNION ALL SELECT 727 UNION ALL SELECT 728 UNION ALL SELECT 729 UNION ALL SELECT 735 UNION ALL SELECT 737 UNION ALL SELECT 743 UNION ALL SELECT 744 UNION ...


8

To find gaps in a number range: Test table and data: mysql> CREATE TABLE wp_blogs -> ( -> blog_id INTEGER -> ); mysql> insert into wp_blogs values(1); mysql> insert into wp_blogs values(2); mysql> insert into wp_blogs values(4); mysql> insert into wp_blogs values(6); mysql> insert into wp_blogs values(7); mysql> ...


7

This is a fairly generic way to do this. Bear in mind it depends on your number column being consecutive. If it's not a Window function and/or CTE type-solution will probably be needed: SELECT number FROM mytable m CROSS JOIN (SELECT 3 AS consec) x WHERE EXISTS (SELECT 1 FROM mytable WHERE number = m.number - ...


6

This is a gaps-and-islands problem. Assuming there are no gaps or duplicates in the same id_set set: WITH partitioned AS ( SELECT *, number - ROW_NUMBER() OVER (PARTITION BY id_set) AS grp FROM atable WHERE status = 'FREE' ), counted AS ( SELECT *, COUNT(*) OVER (PARTITION BY id_set, grp) AS cnt FROM partitioned ) SELECT id_set, ...


5

The merge join works like a zipper - if you don't care about order, SQL Server knows that it can sort the input in any way it wants, and not have to worry about re-ordering anything. When you add the order by, in this case a merge join is no longer the best choice, because materializing and sorting the first CTE twice in the order defined by the ROW_NUMBER() ...


5

A simple and fast variant: SELECT min(number) AS first_number, count(*) AS ct_free FROM ( SELECT *, number - row_number() OVER (PARTITION BY id_set ORDER BY number) AS grp FROM tbl WHERE status = 'FREE' ) x GROUP BY grp HAVING count(*) >= 3 -- minimum length of sequence only goes here ORDER BY grp LIMIT 1; Requires a gapless ...


5

See this discussion on AskTom about the way expressions are evaluated. The decode function does perform short-circuit in most cases: right-side expressions are not evaluated if a condition on the left is evaluated to true. Sequence are special however, they are evaluated for all lines in all cases. The easiest workaround is to use a function that calls the ...


5

Use a different method. For a start, don't populate a temporary table with 2.7M rows - that's not going to want to return in under ten seconds. You could use a CTE instead, and that might work much better. WITH taps as ( SELECT RowID = ROW_NUMBER() OVER (ORDER BY DeviceESN, CreatedDateUTC, [Counter]), DeviceESN, TapDateUTC, CreatedDateUTC, [Counter] ...


5

Well you could do something like this which would avoid the cursor that you had in the query that you originally posted. Firstly, change the definition of your columns table so it looks like this: DECLARE @columns TABLE ( ID INT IDENTITY NOT NULL, NAME NVARCHAR(128) NOT NULL ); Then, using whatever method you have; split the string and populate ...


4

If you create a clustered index on createdDateUTC the query Rob has given could be really fast. Can you try that? The other option would be to add an artificial unique tapDetailID column with a clustered index and use that instead.


4

General solution for this class of problems To get the longest sequence (1 result, longest of all, arbitrary pick if there are ties): SELECT race_id, car_type, count(*) AS seq_len FROM ( SELECT *, count(step OR NULL) OVER (ORDER BY race_id, car_type, lap_no) AS grp FROM ( SELECT *, (lag(lap_no) OVER (PARTITION BY race_id, car_type ORDER BY ...


3

And a 1 second solution... ;WITH cteSource(StartedAt, FinishedAt) AS ( SELECT s.StartedAt, e.FinishedAt FROM ( SELECT StartedAt, ROW_NUMBER() OVER (ORDER BY StartedAt) AS rn FROM dbo.Tasks ) AS s INNER JOIN ( SELECT FinishedAt, ...


3

The following C# code solves the problem: var connString = "Initial Catalog=MyDb;Data Source=MyServer;Integrated Security=SSPI;Application Name=Benchmarks;"; var stopWatch = new Stopwatch(); stopWatch.Start(); using (var conn = new SqlConnection(connString)) { conn.Open(); var command = conn.CreateCommand(); ...


3

This will return only the first of the 3 numbers. It does not require that the values of number are consecutive. Tested at SQL-Fiddle: WITH cte3 AS ( SELECT *, COUNT(CASE WHEN status = 'FREE' THEN 1 END) OVER (PARTITION BY id_set ORDER BY number ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS cnt FROM atable ) SELECT ...


3

This isn't an Update statement, but it does meet the other requirements of the question. MERGE INTO t1 b USING (SELECT letter, number1, number2, Row_Number() OVER (PARTITION BY DECODE(letter,'a',1,'d',1,'e',1,0) ORDER BY number1) SectionRow FROM t1) a ON (a.number1 = b.number1) WHEN MATCHED THEN UPDATE SET b.letter='x', b.number2 = ...


3

You can store pre-calculated gaps, and use constraints to make sure that your pre-calcualted data is always up-to-date: Here is the table and the first interval CREATE TABLE dbo.IntegerSettings(SettingID INT NOT NULL, IntValue INT NOT NULL, StartedAt DATETIME NOT NULL, FinishedAt DATETIME NOT NULL, PreviousFinishedAt DATETIME NULL, ...


2

Recursive CTEs are perfect for this job. You don't need cursors in this case, and I bet a CTE will perform much better than any cursor- or loop-based approach to solving this problem. The following query gives you exactly what you need. I tested it on SQL Server 2008, but if you ignore the setup block and replace @table with the name of your target ...


2

To see what's missing, you can compare to a complete list. There is no row generator in MySQL (like generate_series() in Postgres), but various surrogates are floating around. Like this one in the MySQL forums. Helper table to provide numbers from 0-9: CREATE TABLE int10 (i INT); INSERT INTO int10 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); Building ...


2

I think I have exhausted the limits of my knowledge in SQL server on this one.... For finding a gap in SQL server (what the C# code does), and you don't care about starting or ending gaps (those before the first start, or after the last finish), then the following query (or variants) is the fastest I could find: SELECT e.FinishedAt as GapStart, s.StartedAt ...


2

Here is a solution which runs is 4 seconds. WITH cteRaw(ts, type, e, s) AS ( SELECT StartedAt, 1 AS type, NULL, ROW_NUMBER() OVER (ORDER BY StartedAt) FROM dbo.Tasks UNION ALL SELECT FinishedAt, -1 AS type, ROW_NUMBER() OVER (ORDER BY FinishedAt), NULL FROM dbo.Tasks ), ...


1

Assuming your largest range will be 366 days (and you can adjust that number), I think this approach will scale better than a recursive CTE or a while loop. This only handles days, because it's not quite clear to me from the question what the output should look like, exactly, when the range is months or years. ;WITH x AS ( SELECT TOP (366) n = number ...


1

OK, I've refactored (hopefully for the last time) for your new data and to use a calendar table. In this simple demo I create and populate the calendar table (for years 2000 to 2030), then do the day|month|year code. This version honours the time component and will scale well. The calendar table is one I use in my dev. Please note this is intended as a ...


1

Since you are in 2012 version, here's another way to solve this gaps-and-islands problem that uses the new LAG() function: ; WITH seq AS ( SELECT SiteIDNumber, SequenceNumber, LagSequenceNumber = LAG(SequenceNumber) OVER (PARTITION BY SiteIDNumber ORDER BY SequenceNumber), ...


1

select car_type, race_id, sum(case when lap_no=(prev+1) then 1 else 0 end)+1 seq_len from ( select *, lag(lap_no) over (partition by car_type, race_id order by lap_no) prev from tbl ) z group by car_type, race_id order by seq_len desc limit 1; SQLFiddle here


1

In SQL 2012 you can also do this using LAG and the window functions, eg DECLARE @d TABLE ( signal INT PRIMARY KEY) INSERT INTO @d VALUES ( 724 ), ( 727 ), ( 728 ), ( 729 ), ( 735 ), ( 737 ), ( 743 ), ( 744 ), ( 748 ) SELECT signal , 1 + ( SUM( is_group ) OVER ( ORDER BY signal ROWS BETWEEN 1 PRECEDING AND 0 ...



Only top voted, non community-wiki answers of a minimum length are eligible