diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
commit | 3f619478f796eddbba6e39502fe941b285dd97b1 (patch) | |
tree | e2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/main/win_rank.result | |
parent | Initial commit. (diff) | |
download | mariadb-upstream.tar.xz mariadb-upstream.zip |
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/main/win_rank.result')
-rw-r--r-- | mysql-test/main/win_rank.result | 107 |
1 files changed, 107 insertions, 0 deletions
diff --git a/mysql-test/main/win_rank.result b/mysql-test/main/win_rank.result new file mode 100644 index 00000000..e74c2edd --- /dev/null +++ b/mysql-test/main/win_rank.result @@ -0,0 +1,107 @@ +# +# Try DENSE_RANK() function +# +create table t1 ( +pk int primary key, +a int, +b int +); +insert into t1 values +( 1 , 0, 10), +( 2 , 0, 10), +( 3 , 1, 10), +( 4 , 1, 10), +( 8 , 2, 10), +( 5 , 2, 20), +( 6 , 2, 20), +( 7 , 2, 20), +( 9 , 4, 20), +(10 , 4, 20); +select a, rank() over (order by a) as rank, +dense_rank() over (order by a) as dense_rank +from t1; +a rank dense_rank +0 1 1 +0 1 1 +1 3 2 +1 3 2 +2 5 3 +2 5 3 +2 5 3 +2 5 3 +4 9 4 +4 9 4 +select a, b, rank() over (partition by b order by a) as rank, +dense_rank() over (partition by b order by a) as dense_rank +from t1; +a b rank dense_rank +0 10 1 1 +0 10 1 1 +1 10 3 2 +1 10 3 2 +2 10 5 3 +2 20 1 1 +2 20 1 1 +2 20 1 1 +4 20 4 2 +4 20 4 2 +drop table t1; +# +# Test with null values in the table. +# +create table t2 (s1 int, s2 char(5)); +insert into t2 values (1,'a'); +insert into t2 values (null,null); +insert into t2 values (1,null); +insert into t2 values (null,'a'); +insert into t2 values (null,'c'); +insert into t2 values (2,'b'); +insert into t2 values (-1,''); +select *, rank() over (order by s1) as rank, +dense_rank() over (order by s1) as dense_rank +from t2 +order by s1, s2; +s1 s2 rank dense_rank +NULL NULL 1 1 +NULL a 1 1 +NULL c 1 1 +-1 4 2 +1 NULL 5 3 +1 a 5 3 +2 b 7 4 +select *, rank() over (partition by s2 order by s1) as rank, +dense_rank() over (partition by s2 order by s1) as dense_rank +from t2 +order by s1, s2; +s1 s2 rank dense_rank +NULL NULL 1 1 +NULL a 1 1 +NULL c 1 1 +-1 1 1 +1 NULL 2 2 +1 a 2 2 +2 b 1 1 +select *, rank() over (order by s2) as rank, +dense_rank() over (order by s2) as dense_rank +from t2 +order by s2, s1; +s1 s2 rank dense_rank +NULL NULL 1 1 +1 NULL 1 1 +-1 3 2 +NULL a 4 3 +1 a 4 3 +2 b 6 4 +NULL c 7 5 +select *, rank() over (partition by s1 order by s2) as rank, +dense_rank() over (partition by s1 order by s2) as dense_rank +from t2; +s1 s2 rank dense_rank +NULL NULL 1 1 +NULL a 2 2 +NULL c 3 3 +-1 1 1 +1 NULL 1 1 +1 a 2 2 +2 b 1 1 +drop table t2; |