By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
(No column name) |
---|
Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13.0.5698.0 (X64) Feb 15 2020 01:47:30 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
7 rows affected
col_name | col_value | cnt | (No column name) |
---|---|---|---|
first | apple | 3 | 1 |
first | banana | 3 | 2 |
second | apple | 3 | 1 |
second | banana | 3 | 2 |
third | apple | 3 | 1 |
third | banana | 3 | 2 |
third | orange | 1 | 3 |
col_name | col_value | cnt |
---|---|---|
third | orange | 1 |
col_name | col_value | status |
---|---|---|
first | apple | Common |
second | apple | Common |
third | apple | Common |
third | banana | Common |
second | banana | Common |
first | banana | Common |
third | orange | Uncommon |
col_value | cnt |
---|---|
apple | 3 |
banana | 3 |
orange | 1 |
col_value | cnt |
---|---|
orange | 1 |
col_name | count_col | col_value | count_val |
---|---|---|---|
first | 7 | apple | 3 |
second | 7 | apple | 3 |
third | 7 | apple | 3 |
third | 7 | banana | 3 |
second | 7 | banana | 3 |
first | 7 | banana | 3 |
third | 7 | orange | 1 |
col_name | col_value | status |
---|---|---|
first | apple | Not Common |
second | apple | Not Common |
third | apple | Not Common |
third | banana | Not Common |
second | banana | Not Common |
first | banana | Not Common |
third | orange | Not Common |
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
StmtText | StmtId | NodeId | Parent | PhysicalOp | LogicalOp | Argument | DefinedValues | EstimateRows | EstimateIO | EstimateCPU | AvgRowSize | TotalSubtreeCost | OutputList | Warnings | Type | Parallel | EstimateExecutions |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
WITH cte1 AS ( SELECT col_name, col_value, COUNT(col_value) OVER (PARTITION BY col_value) AS cnt -- COUNT(col_value) OVER (PARTITION BY col_name ORDER BY col_value) FROM ptest ), cte2 AS ( SELECT MAX (cnt) AS mcnt FROM cte1 ) SELECT * FROM cte1 WHERE cnt < (SELECT mcnt FROM cte2); |
1 | 1 | 0 | null | null | 1 | null | 7 | null | null | null | 0.02971825 | null | null | SELECT | False | null |
|--Nested Loops(Inner Join, WHERE:([Expr1003]<[Expr1008])) | 1 | 2 | 1 | Nested Loops | Inner Join | WHERE:([Expr1003]<[Expr1008]) | null | 7 | 0 | 2.926E-05 | 47 | 0.02971825 | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_name], [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value], [Expr1003] | null | PLAN_ROW | False | 1 |
|--Stream Aggregate(DEFINE:([Expr1008]=MAX([Expr1007]))) | 1 | 3 | 2 | Stream Aggregate | Aggregate | null | [Expr1008]=MAX([Expr1007]) | 1 | 0 | 4.7E-06 | 11 | 0.01484579 | [Expr1008] | null | PLAN_ROW | False | 1 |
| |--Nested Loops(Inner Join) | 1 | 4 | 3 | Nested Loops | Inner Join | null | null | 7 | 0 | 0.0001227688 | 11 | 0.01484109 | [Expr1007] | null | PLAN_ROW | False | 1 |
| |--Table Spool | 1 | 5 | 4 | Table Spool | Lazy Spool | null | null | 3 | 0 | 0 | 36 | 0.01471354 | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] | null | PLAN_ROW | False | 1 |
| | |--Segment | 1 | 6 | 5 | Segment | Segment | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] | null | 7 | 0 | 1.5944E-05 | 36 | 0.0146976 | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value], [Segment1011] | null | PLAN_ROW | False | 1 |
| | |--Sort(ORDER BY:([fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] ASC)) | 1 | 7 | 6 | Sort | Sort | ORDER BY:([fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] ASC) | null | 7 | 0.01126126 | 0.0001306923 | 36 | 0.01468165 | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] | null | PLAN_ROW | False | 1 |
| | |--Index Scan(OBJECT:([fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[name_value_uq])) | 1 | 8 | 7 | Index Scan | Index Scan | OBJECT:([fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[name_value_uq]) | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] | 7 | 0.003125 | 0.0001647 | 36 | 0.0032897 | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] | null | PLAN_ROW | False | 1 |
| |--Nested Loops(Inner Join, WHERE:((1))) | 1 | 9 | 4 | Nested Loops | Inner Join | WHERE:((1)) | null | 2.333333 | 0 | 1.5944E-06 | 36 | 3.1888E-06 | [Expr1007] | null | PLAN_ROW | False | 4 |
| |--Compute Scalar(DEFINE:([Expr1007]=CONVERT_IMPLICIT(int,[Expr1012],0))) | 1 | 10 | 9 | Compute Scalar | Compute Scalar | DEFINE:([Expr1007]=CONVERT_IMPLICIT(int,[Expr1012],0)) | [Expr1007]=CONVERT_IMPLICIT(int,[Expr1012],0) | 1 | 0 | 1.5944E-07 | 36 | 1.75384E-06 | [Expr1007], [Expr1007] | null | PLAN_ROW | False | 4 |
| | |--Stream Aggregate(DEFINE:([Expr1012]=Count(*))) | 1 | 11 | 10 | Stream Aggregate | Aggregate | null | [Expr1012]=Count(*) | 1 | 0 | 1.5944E-06 | 36 | 1.5944E-06 | [Expr1012] | null | PLAN_ROW | False | 4 |
| | |--Table Spool | 1 | 12 | 11 | Table Spool | Lazy Spool | null | null | 2.333333 | 0 | 0 | 36 | 0 | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] | null | PLAN_ROW | False | 4 |
| |--Table Spool | 1 | 13 | 9 | Table Spool | Lazy Spool | null | null | 2.333333 | 0 | 0 | 36 | 0 | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] | null | PLAN_ROW | False | 4 |
|--Nested Loops(Inner Join) | 1 | 14 | 2 | Nested Loops | Inner Join | null | null | 7 | 0 | 0.0001227688 | 47 | 0.0148411 | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_name], [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value], [Expr1003] | null | PLAN_ROW | False | 1 |
|--Table Spool | 1 | 15 | 14 | Table Spool | Lazy Spool | null | null | 3 | 0 | 0 | 43 | 0.01471355 | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_name], [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] | null | PLAN_ROW | False | 1 |
| |--Segment | 1 | 16 | 15 | Segment | Segment | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] | null | 7 | 0 | 1.5944E-05 | 43 | 0.0146976 | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_name], [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value], [Segment1013] | null | PLAN_ROW | False | 1 |
| |--Sort(ORDER BY:([fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] ASC)) | 1 | 17 | 16 | Sort | Sort | ORDER BY:([fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] ASC) | null | 7 | 0.01126126 | 0.0001306993 | 43 | 0.01468166 | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_name], [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] | null | PLAN_ROW | False | 1 |
| |--Index Scan(OBJECT:([fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[name_value_uq])) | 1 | 18 | 17 | Index Scan | Index Scan | OBJECT:([fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[name_value_uq]) | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_name], [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] | 7 | 0.003125 | 0.0001647 | 43 | 0.0032897 | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_name], [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] | null | PLAN_ROW | False | 1 |
|--Nested Loops(Inner Join, WHERE:((1))) | 1 | 19 | 14 | Nested Loops | Inner Join | WHERE:((1)) | null | 2.333333 | 0 | 1.5944E-06 | 43 | 3.1888E-06 | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_name], [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value], [Expr1003] | null | PLAN_ROW | False | 4 |
|--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1014],0))) | 1 | 20 | 19 | Compute Scalar | Compute Scalar | DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1014],0)) | [Expr1003]=CONVERT_IMPLICIT(int,[Expr1014],0) | 1 | 0 | 1.5944E-07 | 43 | 1.75384E-06 | [Expr1003], [Expr1003] | null | PLAN_ROW | False | 4 |
| |--Stream Aggregate(DEFINE:([Expr1014]=Count(*))) | 1 | 21 | 20 | Stream Aggregate | Aggregate | null | [Expr1014]=Count(*) | 1 | 0 | 1.5944E-06 | 43 | 1.5944E-06 | [Expr1014] | null | PLAN_ROW | False | 4 |
| |--Table Spool | 1 | 22 | 21 | Table Spool | Lazy Spool | null | null | 2.333333 | 0 | 0 | 43 | 0 | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_name], [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] | null | PLAN_ROW | False | 4 |
|--Table Spool | 1 | 23 | 19 | Table Spool | Lazy Spool | null | null | 2.333333 | 0 | 0 | 43 | 0 | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_name], [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] | null | PLAN_ROW | False | 4 |
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
StmtText | StmtId | NodeId | Parent | PhysicalOp | LogicalOp | Argument | DefinedValues | EstimateRows | EstimateIO | EstimateCPU | AvgRowSize | TotalSubtreeCost | OutputList | Warnings | Type | Parallel | EstimateExecutions |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
SELECT * FROM ( SELECT col_value, COUNT(col_value) AS cnt FROM ptest GROUP BY col_value ) AS t WHERE cnt < ( SELECT MAX(cnt) FROM ( SELECT col_value, COUNT(col_value) AS cnt FROM ptest GROUP BY col_value ) AS u ); |
1 | 1 | 0 | null | null | 1 | null | 3 | null | null | null | 0.02939043 | null | null | SELECT | False | null |
|--Nested Loops(Inner Join, WHERE:([Expr1003]<[Expr1008])) | 1 | 2 | 1 | Nested Loops | Inner Join | WHERE:([Expr1003]<[Expr1008]) | null | 3 | 0 | 1.254E-05 | 20 | 0.02939043 | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value], [Expr1003] | null | PLAN_ROW | False | 1 |
|--Stream Aggregate(DEFINE:([Expr1008]=MAX([Expr1007]))) | 1 | 3 | 2 | Stream Aggregate | Aggregate | null | [Expr1008]=MAX([Expr1007]) | 1 | 0 | 2.3E-06 | 11 | 0.01468965 | [Expr1008] | null | PLAN_ROW | False | 1 |
| |--Compute Scalar(DEFINE:([Expr1007]=CONVERT_IMPLICIT(int,[Expr1015],0))) | 1 | 4 | 3 | Compute Scalar | Compute Scalar | DEFINE:([Expr1007]=CONVERT_IMPLICIT(int,[Expr1015],0)) | [Expr1007]=CONVERT_IMPLICIT(int,[Expr1015],0) | 3 | 0 | 0 | 11 | 0.01468735 | [Expr1007] | null | PLAN_ROW | False | 1 |
| |--Stream Aggregate(GROUP BY:([fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value]) DEFINE:([Expr1015]=Count(*))) | 1 | 5 | 4 | Stream Aggregate | Aggregate | GROUP BY:([fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value]) | [Expr1015]=Count(*) | 3 | 0 | 5.7E-06 | 11 | 0.01468735 | [Expr1015] | null | PLAN_ROW | False | 1 |
| |--Sort(ORDER BY:([fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] ASC)) | 1 | 6 | 5 | Sort | Sort | ORDER BY:([fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] ASC) | null | 7 | 0.01126126 | 0.0001306923 | 36 | 0.01468165 | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] | null | PLAN_ROW | False | 1 |
| |--Index Scan(OBJECT:([fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[name_value_uq])) | 1 | 7 | 6 | Index Scan | Index Scan | OBJECT:([fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[name_value_uq]) | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] | 7 | 0.003125 | 0.0001647 | 36 | 0.0032897 | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] | null | PLAN_ROW | False | 1 |
|--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1016],0))) | 1 | 8 | 2 | Compute Scalar | Compute Scalar | DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1016],0)) | [Expr1003]=CONVERT_IMPLICIT(int,[Expr1016],0) | 3 | 0 | 0 | 20 | 0.01468733 | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value], [Expr1003] | null | PLAN_ROW | False | 1 |
|--Stream Aggregate(GROUP BY:([fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value]) DEFINE:([Expr1016]=Count(*))) | 1 | 9 | 8 | Stream Aggregate | Aggregate | GROUP BY:([fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value]) | [Expr1016]=Count(*) | 3 | 0 | 5.7E-06 | 20 | 0.01468733 | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value], [Expr1016] | null | PLAN_ROW | False | 1 |
|--Sort(ORDER BY:([fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] ASC)) | 1 | 10 | 9 | Sort | Sort | ORDER BY:([fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] ASC) | null | 7 | 0.01126126 | 0.0001306723 | 16 | 0.01468163 | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] | null | PLAN_ROW | False | 1 |
|--Index Scan(OBJECT:([fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[name_value_uq])) | 1 | 11 | 10 | Index Scan | Index Scan | OBJECT:([fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[name_value_uq]) | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] | 7 | 0.003125 | 0.0001647 | 16 | 0.0032897 | [fiddle_fbca6452c0d046b7a1ab025413f8696a].[dbo].[ptest].[col_value] | null | PLAN_ROW | False | 1 |
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.