sql - Select all columns with only two distinct columns

Question

In my SQL Server database, I have a table with many duplicate values and I need to fetch results with distinct columns EID and YEAR and select rows containing fewer NULL values or order the table and get a final DISTINCT column EID and YEAR rows.

For example: below the table with EID = E138442 and YEAR = 2019 occurs 21 times were in this duplicate the row containing fewer null values should be fetched

+---------+------+------+------+------+------+------+------+------+------+------+------+------+------+
|   EID   | YEAR | JAN  | FEB  | MAR  | APR  | MAY  | JUN  | JUL  | AUG  | SEP  | OCT  | NOV  | DEC  |
+---------+------+------+------+------+------+------+------+------+------+------+------+------+------+
| E050339 | 2019 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1    |
| E050339 | 2020 | NULL | 6    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| E050339 | 2020 | 13   | 6    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| E138348 | 2019 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1    | NULL |
| E138348 | 2019 | NULL | 1    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| e138372 | 2019 | 1    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| E138440 | 2019 | NULL | NULL | 2    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| E138442 | 2019 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 5    |
| E138442 | 2019 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 9    | 5    |
| E138442 | 2019 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 9    | 5    |
| E138442 | 2019 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 4    | 9    | 5    |
| E138442 | 2019 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 4    | 9    | 5    |
| E138442 | 2019 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 4    | 9    | 5    |
| E138442 | 2019 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 4    | 9    | 5    |
| E138442 | 2019 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 4    | 9    | 5    |
| E138442 | 2019 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 4    | 4    | 9    | 5    |
| E138442 | 2019 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 7    | 4    | 4    | 9    | 5    |
| E138442 | 2019 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 7    | 4    | 4    | 9    | 5    |
| E138442 | 2019 | NULL | NULL | NULL | NULL | NULL | NULL | 7    | 7    | 4    | 4    | 9    | 5    |
| E138442 | 2019 | NULL | NULL | NULL | NULL | NULL | NULL | 7    | 7    | 4    | 4    | 9    | 5    |
| E138442 | 2019 | NULL | NULL | NULL | NULL | NULL | 2    | 7    | 7    | 4    | 4    | 9    | 5    |
| E138442 | 2019 | NULL | NULL | NULL | NULL | 7    | 2    | 7    | 7    | 4    | 4    | 9    | 5    |
| E138442 | 2019 | NULL | NULL | NULL | 7    | 7    | 2    | 7    | 7    | 4    | 4    | 9    | 5    |
| E138442 | 2019 | NULL | NULL | 1    | 7    | 7    | 2    | 7    | 7    | 4    | 4    | 9    | 5    |
| E138442 | 2019 | NULL | 1    | NULL | 7    | 7    | 2    | 7    | 7    | 4    | 4    | 9    | 5    |
| E138442 | 2020 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1    |
| E138442 | 2020 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1    |
| E138442 | 2020 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1    |
| E138442 | 2020 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1    |
+---------+------+------+------+------+------+------+------+------+------+------+------+------+------+

I need a SQL query to fetch values as shown here:

+---------+------+------+------+------+------+------+------+------+------+------+------+------+------+
|   EID   | YEAR | JAN  | FEB  | MAR  | APR  | MAY  | JUN  | JUL  | AUG  | SEP  | OCT  | NOV  | DEC  |
+---------+------+------+------+------+------+------+------+------+------+------+------+------+------+
| E050339 | 2020 | 13   | 6    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| E138348 | 2019 | NULL | 1    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| e138372 | 2019 | 1    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| E138440 | 2019 | NULL | NULL | 2    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| E138442 | 2019 | NULL | 1    | NULL | 7    | 7    | 2    | 7    | 7    | 4    | 4    | 9    | 5    |
| E138442 | 2020 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1    |
+---------+------+------+------+------+------+------+------+------+------+------+------+------+------+

The result table should have a final row with distinct columns EID and YEAR.

SELECT *
FROM TABLE_NAME C1 
WHERE EXISTS (SELECT 1
              FROM TABLE_NAME C2
              WHERE C1.EID = C2.EID AND C1.YEAR = C2.YEAR 
              HAVING COUNT(*) = 1)
ORDER BY 
    c1.EID, c1.YEAR, c1.JAN, c1.FEB, c1.MAR, c1.APR,
    c1.MAY, c1.JUN, c1.JUL, c1.AUG, c1.SEP, c1.OCT, c1.NOV, c1.DEC ASC;  

I tried the above code but found irrelevant results

question from:https://stackoverflow.com/questions/65942999/select-all-columns-with-only-two-distinct-columns

Answer 1

since you have no other way to distinguish members of a group and based on "select rows containing fewer NULL values " here is one way how you can do it by using ctes, its not clean but probably the only way:

with cte as (
SELECT *,
ISNULL(c1.JAN, 1) + ISNULL(c1.FEB,1) +  ... + ISNULL(c1.DEC,1) AS NullCount
FROM 
tablename
) 
, cte2 as (
select EID   , YEAR , min(NullCount)  min_nullcount
from cte 
group by EID   , YEAR 
)

select t.* 
from 
cte t
join cte2 tt
 on t.EID = tt.EID
 and t.YEAR = tt.YEAR
 and t.NULLCount = tt.min_nullcount

If you have duplicate minimum null per group you can use query below :

select * from (
SELECT *,
ROW_NUMBER OVER (partition by EID , YEAR order by ISNULL(c1.JAN, 1) +  ... + ISNULL(c1.DEC,1) AS rnk
FROM 
tablename
) xx
WHERE rnk = 1