Sql-server – Returning rows where all non-NULL values are equal

pivotsql servert-sql

Is there a simpler T-SQL construct for "all of these columns to be equal (NULLs ignored) on a row" – effective I want to say:

WHERE MIN(a, b, c) = MAX(a, b, c) OR COALESCE(a, b, c) IS NULL

this would be the equivalent of the COALESCEs of all the permutations to be equal – but there is no non-aggregating MIN/MAX function

An alternative for 3 columns is:

WHERE ( 
COALESCE(a, b, c) = COALESCE(b, c, a) 
AND COALESCE(a, b, c) = COALESCE(c, a, b) 
AND COALESCE(a, b, c) = COALESCE(b, a, c) 
AND COALESCE(a, b, c) = COALESCE(a, c, b) 
AND COALESCE(a, b, c) = COALESCE(c, b, a) 
) 
OR COALESCE(a, b, c) IS NULL

i.e. "All non-NULL columns a, b, c must be equal or all columns may be null"

Obviously a PIVOT/UNPIVOT implementation could be done or a complex CASE statement, but I'm looking for something relatively simple.

In this case, the columns are all integers, so I guess there is probably a math trick I could figure out.

Best Answer

The most compact syntax I can find is:

SELECT * 
FROM @T AS t
WHERE EXISTS 
(
    SELECT ISNULL(ISNULL(a, b), c) 
    INTERSECT 
    SELECT ISNULL(ISNULL(b, c), a) 
    INTERSECT 
    SELECT ISNULL(ISNULL(c, a), b)
);

Based on an idea from one of my old blog posts that describes how to use INTERSECT and EXCEPT to replace comparisons like a <> b OR (a IS NULL AND b IS NULL) with NOT EXISTS (a INTERSECT b).

Related Solutions

Sql-server – Improve query performance when selecting almost all rows with many “group by” columns

The non-clustered index you have tested is not the best for this query. It can be used for the WHERE clause and for doing an index scan instead of a full table scan but it cannot be used for the GROUP BY.

The best possible index would have to be a partial index (to filter the unwanted rows from the WHERE clause), then have all the columns used in the GROUP BY and then INCLUDE all the other columns used in the SELECT:

CREATE INDEX special_ix 
  ON dbo.Commissions_Output
    ( company, location, account, 
      salesroute, employee, producttype, 
      item, loadjdate, commissionrate ) 
INCLUDE 
  ( [Extended Sales Price], [Delivered Qty] ) 
WHERE 
  ( [Extended Sales Price] <> 0 ) ;

Sql-server – Sum values from child tables and ignore equal rows

Like @ypercube suggest, aggregate both sides separately, then join them.

In the following code, I've intentionally used LEFT JOIN to cater for the possibility that a project may not have a benefit or cost.

WITH ben AS (
    SELECT pbc.project_id, SUM(b.project_benefit) AS TOTAL_BENEFIT
    FROM Benefit AS b
    INNER JOIN ProjectBenefitCost AS pbc ON b.benefitcost_id=pbc.id
    GROUP BY pbc.project_id),

     cost AS (
    SELECT pbc.project_id, SUM(c.project_cost) AS TOTAL_COST
    FROM Cost AS c
    INNER JOIN ProjectBenefitCost AS pbc ON c.benefitcost_id=pbc.id
    GROUP BY pbc.project_id)

SELECT P.id, P.name, cost.TOTAL_COST, ben.TOTAL_BENEFIT
FROM Project AS P
LEFT JOIN ben ON P.id=ben.project_id
LEFT JOIN cost ON P.id=cost.project_id;

Best Answer

Related Solutions

Sql-server – Improve query performance when selecting almost all rows with many “group by” columns

Sql-server – Sum values from child tables and ignore equal rows

Related Question