Tag Info

Hot answers tagged

13

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 ...


7

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

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

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 ...


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

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 ...


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

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

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

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 = ...


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

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

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

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



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