add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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.