Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Identify record sequences matching a predefined pattern in SQL Server
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
IF OBJECT_ID('dbo.mytable', 'U') IS NOT NULL BEGIN DROP TABLE dbo.mytable END CREATE TABLE mytable ([Id] int, [Val] int) ; INSERT INTO mytable ([Id], [Val]) VALUES (1, 5), (2, 2), (3, 6), (4, 8), (5, 5), (6, 2), (7, 5), (8, 2), (9, 6), (10, 8) ; ;WITH Seq AS ( SELECT v, ROW_NUMBER() OVER(ORDER BY k) AS rn FROM (VALUES(1, 5), (2, 2), (3, 6), (4, 8)) x(k,v) ), Grp AS ( SELECT Id, Val, ROW_NUMBER() OVER (ORDER BY Id) - rn AS grp FROM mytable AS m LEFT JOIN Seq AS s ON m.Val = s.v ) SELECT g1.Id, g1.Val FROM Grp AS g1 INNER JOIN ( SELECT grp FROM Grp GROUP BY grp HAVING COUNT(*) = 4 ) AS g2 ON g1.grp = g2.grp DROP TABLE dbo.mytable
View schema
Execution time: 0,03 sec, rows selected: 8, rows affected: 10, absolute service time: 0,19 sec
edit mode
|
history
Id
Val
1
1
5
2
2
2
3
3
6
4
4
8
5
7
5
6
8
2
7
9
6
8
10
8