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


13

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


9

You can use outer apply the get the lead and lag values and in the column list you can use datediff to see if the gap is something other than one day. select T1.ID, case when datediff(day, Lag.Start, T1.Start) = 1 then T1.Start else dateadd(hour, 9, T1.Start) end as Start, case when datediff(day, T1.Start, ...


9

This type of requirement comes under the banner of "gaps and islands". A popular approach is WITH T AS (SELECT *, DENSE_RANK() OVER (PARTITION BY ItemId ORDER BY DateOfChange) - DENSE_RANK() OVER (PARTITION BY ItemId, Status ORDER BY DateOfChange) AS Grp FROM ItemTable) SELECT ItemId, Status, ...


8

Given the following sample data: DECLARE @Data AS table ( data integer PRIMARY KEY ); INSERT @Data (data) VALUES (1), (2), (3), (6), (7), (15), (16); One way to achieve the result you are after is: WITH Grouped AS ( -- Identify groups SELECT D.data, grp = D.data - ROW_NUMBER() ...


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


7

First off, gaps in a sequence are to be expected. Ask yourself if you really need to remove them. Your life gets simpler if you just live with it. To get gap-less numbers, the (often better) alternative is to use a VIEW with row_number(). Example in this related answer: Gap-less sequence where multiple transactions with multiple tables are involved Here ...


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

create table tbl (lap_no int, car_type text, race_id int); insert into tbl values (1,'red',1),(2,'red',1),(3,'red',1),(4,'red',1), (1,'blue',1),(5,'red',1),(2,'blue',1),(1,'green',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 ...


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

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


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

So I've built a short SQL script to do this operation, based on the original sample input data: Order_year, Contact 2015, 123 2014, 123 2013, 123 2011, 123 2010, 123 2015, 234 2014, 234 2011, 234 The script is as follows: WITH RECURSIVE ranked_order AS( SELECT order_year, contact, 1 AS counter FROM orders UNION SELECT orders.order_year, ...


3

If this is a table of back-to-back ranges only, your case can be treated as a classic "gaps and islands" problem, where you just need to isolate islands of consecutive ranges and then "condense" them by taking the minimum [from] and the maximum [to] per island. There is an established method of solving this using two ROW_NUMBER calls: WITH islands AS ( ...


3

Since MySQL doesn't support ROW_NUMBER(), You can use a variable to create a group like this. Sample Data CREATE TABLE `Rings` ( ID_RingType CHAR(2), Number MEDIUMINT UNSIGNED, ID_User INT(11) ); INSERT INTO `Rings` VALUES ('AA',1,1), ('AA',2,1), ('AA',3,1), ('AA',11,1), ('AA',12,1), ('AA',13,1), ('AA',14,1), ('AA',15,1), ...


3

The answer with the variables is going to be more efficient but here is an answer with pure SQL: select a.id_user, a.id_ringtype, a.number as min, min(b.number) as max from rings as a join rings as b on a.id_user = b.id_user and a.id_ringtype = b.id_ringtype and a.number <= b.number where not exists ...


3

SELECT location, employee, min(created_at) AS start_ts, max(created_at) AS end_ts FROM ( SELECT location, employee, created_at , row_number() OVER (PARTITION BY location ORDER BY created_at) - row_number() OVER (PARTITION BY location, employee ORDER BY created_at) AS grp FROM employee_logs ) sub GROUP BY location, employee, grp ...


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


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

SELECT person, max(ct) AS max_ct FROM ( SELECT person, count(*) AS ct FROM ( SELECT person, result , row_number() OVER (PARTITION BY person ORDER BY time) - row_number() OVER (PARTITION BY person, result ORDER BY time) AS grp FROM tablex ) sub1 GROUP BY person, result, grp ) sub2 GROUP BY person ORDER ...



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