Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Coalesce returns NULL
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
CREATE TABLE #TEST_COALESCE ( Id int NOT NULL, DateTest datetime NOT NULL, PRIMARY KEY (Id, DateTest) ); INSERT INTO #TEST_COALESCE VALUES (1, '20170201'), (1, '20170202'), (1, '20170203'), (2, '20170204'), (2, '20170205'), (2, '20170206'); SELECT t1.Id, t1.DateTest, (SELECT TOP 1 COALESCE(t2.DateTest, t1.DateTest) FROM #TEST_COALESCE t2 WHERE t2.Id = t1.Id AND t2.DateTest > t1.DateTest ORDER BY t2.Id, t2.DateTest) NextDate FROM #TEST_COALESCE t1; SELECT t1.Id, t1.DateTest, COALESCE((SELECT TOP 1 t2.DateTest FROM #TEST_COALESCE t2 WHERE t2.Id = t1.Id AND t2.DateTest > t1.DateTest ORDER BY t2.Id, t2.DateTest), t1.DateTest) NextDate FROM #TEST_COALESCE t1; DROP TABLE #TEST_COALESCE;
View schema
Execution time: 0,02 sec, rows selected: 12, rows affected: 6, absolute service time: 0,17 sec
edit mode
|
history
Id
DateTest
NextDate
1
1
01.02.2017 00:00:00
02.02.2017 00:00:00
2
1
02.02.2017 00:00:00
03.02.2017 00:00:00
3
1
03.02.2017 00:00:00
NULL
4
2
04.02.2017 00:00:00
05.02.2017 00:00:00
5
2
05.02.2017 00:00:00
06.02.2017 00:00:00
6
2
06.02.2017 00:00:00
NULL
Id
DateTest
NextDate
1
1
01.02.2017 00:00:00
02.02.2017 00:00:00
2
1
02.02.2017 00:00:00
03.02.2017 00:00:00
3
1
03.02.2017 00:00:00
03.02.2017 00:00:00
4
2
04.02.2017 00:00:00
05.02.2017 00:00:00
5
2
05.02.2017 00:00:00
06.02.2017 00:00:00
6
2
06.02.2017 00:00:00
06.02.2017 00:00:00