Get only single record from a group with various conditons

• If an employee has both eligible and not eligible get the eligible record with min of Cal.
• If an employee has only eligible or not eligible get the record with min of Cal.
• For the above both conditions, we should get only one record for each employee.

DECLARE @Temp TABLE(EmpID VARCHAR(50), ISEligible CHAR(1), Cal INT)
        
        INSERT INTO @Temp(EmpID, ISEligible, Cal)
        SELECT '8829', 'Y', 10   UNION ALL
        SELECT '8829', 'N', 3    UNION ALL
        SELECT '8829', 'Y', 8    UNION ALL
        SELECT '1256', 'Y', 0    UNION ALL
        SELECT '1256', 'N', NULL UNION ALL
        SELECT '1111', 'N', NULL UNION ALL
        SELECT '1111', 'N', NULL UNION ALL
        SELECT '2222', 'Y', NULL UNION ALL
        SELECT '2222', 'Y', NULL
        
        SELECT * FROM @Temp
        
        SELECT *
        FROM  ( SELECT  ROW_NUMBER() OVER(PARTITION BY EmpID 
                   ORDER BY ISEligible DESC, Cal asc) RowNum,
                        EmpID     ,
                        ISEligible,
                        Cal
               FROM    @Temp
               )
               AS Temp
       WHERE  RowNum = 1

Creating custom sort rules for SQL Server

-- Create a Location table 
DECLARE @LOCATION TABLE 
  ( 
     ID           INT IDENTITY(1, 1), 
     LocationName VARCHAR(50) 
  ) 

-- Insert dummy data 
INSERT INTO @Location
SELECT 'Chitoor'  UNION 
SELECT 'Idappadi'  UNION 
SELECT 'Salem'   UNION 
SELECT 'Savadipalayam' UNION 
SELECT 'Sankri'   UNION 
SELECT 'Morasapatti' UNION 
SELECT 'Polampatti' 

-- SELECT with plain our custom sort rules 
SELECT * 
FROM   @Location 
ORDER  BY CASE LocationName 
            WHEN 'Salem' THEN 1 
            WHEN 'Idappadi' THEN 2 
            WHEN 'Chitoor' THEN 3 
            WHEN 'Savadipalayam' THEN 4 
            ELSE 5000 
          END 

The column value which have the sort order, will order by the sort value in ascending.
If the column value which didn't have no sort order will order first Try checking by removing the else part.
If we want to use like clause in the ORDER BY statement, then how?
Move the sort columnName from case to when

SELECT * 
FROM   @Location 
ORDER  BY CASE  
            WHEN LocationName = 'Salem' THEN 1 
            WHEN LocationName = 'Idappadi' THEN 2 
            WHEN LocationName LIKE '%C%' THEN 3 
            WHEN LocationName =  'Savadipalayam' THEN 4 
            ELSE 5000 
          END 

ISNULL + NULLIF instead of CASE WHEN

Difference between NULL and (Empty or ' ')
ISNULL() vs COALESCE()

ISNULL ( <check_expression>, <replacement_value>)
Replaces check_expression with the specified replacement_value, when check_expression is null

NULLIF(<expression>, <expression>)

• Returns a null value if the two specified expressions are equal.
• Returns the first expression if the two expressions are not equal.
• ReturnType is the type of the first expression
• The type of the first argument to NULLIF cannot be the NULL constant because the type of the first argument has to be known.

DECLARE @EmpID VARCHAR(50)
SELECT
       CASE
              WHEN @EmpID IS NULL
              THEN 'rkbabu'
              ELSE @EmpID
       END

SELECT ISNULL(NULLIF(@EmpID, NULL), 'rkbabu')

SELECT NULLIF(10,20) ------> 10
SELECT NULLIF(10, NULL) ------> 10
SELECT NULLIF(10, 10) -------> NULL
SELECT NULLIF('', '     ') ----------> NULL 
SELECT NULLIF('@', '@@') ------> @

SELECT NULLIF(NULL, 10) ------> ERROR -> None of the result expressions in a CASE specification can be NULL.
SELECT NULLIF(NULL, NULL) ------> ERROR -> None of the result expressions in a CASE specification can be NULL.

DECLARE @EmpID VARCHAR(50)
SELECT NULLIF(@EmpID, 10) ----------> NULL
SELECT NULLIF(@EmpID, @EmpID) ----------> NULL

SELECT CASE WHEN NULL = 10 THEN NULL ELSE 10 END ------> 10
SELECT CASE WHEN 10 = NULL THEN NULL ELSE 10 END ------> 10
SELECT CASE WHEN NULL = NULL THEN NULL ELSE 10 END ------> 10 Since one null not equal to another null

Consider some time we will use case in the where condition, this can be replaced with
ISNULL and NULLIF function

DECLARE @Column1 INT, @Column2 INT

SET @Column1 = -1;

SELECT *
FROM tableName
WHERE Column1 = CASE WHEN @Column1 = -1 THEN Column1  ELSE @Column1 END
AND Column2 = CASE WHEN @Column2 IS NOT NULL THEN @Column2 ELSE Column2 END

SELECT *
FROM tableName
WHERE Column1 = ISNULL(NULLIF(@Column1, -1), Column1)
AND Column2 = ISNULL(NULLIF(@Column2, NULL), Column2)