Find max value in a group based on various criteria

Question

I have this table:

Level	Code	Hours
1	Code1	1000
2	Code1	134
3	Code1	1100
3	Code1	1300
4	Code1	234
4	Code1	1234
5	Code1	34
6	Code1	12
1	Code2	300
2	Code2	400
5	Code2	500
5	Code2	550
5	Code2	700
6	Code2	600
1	Code3	100
2	Code3	350
1	Code4	7200
5	Code4	150

If there are records for Level 3 and/or 4 then I need the max value between these level for the code. If level 3 and 4 are not present for the code then get max value of level 5 and/or 6. If neither of level 3, 4, 5 and 6 are present then the query should return max value of level 1 and/or 2.

So the result of above table should be like:

Level	Code	Hours
3	Code1	1300
5	Code2	700
2	Code3	350
5	Code4	150

I have tried this:

SELECT * INTO #tempMaxTimeEntryData FROM
(SELECT LEVEL, CODE, HOURS = MAX(HOURS) FROM tempTimeEntryData 
 GROUP BY LEVEL, CODE) maxtemp

SELECT CODE, HOURS FROM #tempMaxTimeEntryData GROUP BY CODE
HAVING MAX(HOURS) = 
(SELECT HOURS= MAX(HOURS),CODE FROM #tempMaxTimeEntryData 
 WHERE 1 = (CASE WHEN LEVEL IN (3.0,4.0) THEN 1
               WHEN LEVEL NOT IN (3.0,4.0) AND LEVEL IN (5.0,6.0) THEN 1
               WHEN LEVEL NOT IN (3.0,4.0,5.0,6.0) AND LEVEL IN (1.0,2.0) THEN 1
               END) GROUP BY CODE)

This is returning wrong data for Code4. Instead of Level 5, it is returning below:

Level	Code	Hours
1	Code4	7200

New data:

Employee	Level	Code	Hours
Danilo	2	Code1	1080
Akash	1	Code2	82.7
Shradha	2	Code2	838.7
Shalini	3	Code2	813.6
Priyanka	3.5	Code2	2684.4
Gagan	4	Code2	3240
Manu	5	Code2	870
Akash	1	Code3	604.2
Shradha	2	Code3	3372
Manu	5	Code3	1230
Ashu	6	Code3	2270
Akash	1	Code4	448.8
Shradha	2	Code4	2375.4
Shalini	3	Code4	2811
Priyanka	3	Code4	2811
Ritu	4	Code4	85.2
Praveen	5	Code4	67.2
Rakesh	1	Code5	47580
Sampath	5	Code5	339

Output should be:

Employee	Level	Code	Hours
Danilo	2	Code1	1080
Gagan	4	Code2	3240
Ashu	6	Code3	2270
Priyanka	3	Code4	2811
Sampath	5	Code5	339

Answer 1

You just want to apply an order to the rows (per code) and then pick out the ones at the top:

WITH Data as (
    SELECT Code, Level, Hours,
        ROW_NUMBER() OVER (
            PARTITION BY Code ORDER BY
                CASE WHEN Level IN (3, 4) THEN 1
                     WHEN Level IN (5, 6) THEN 2
                     WHEN Level IN (1, 2) THEN 3 END,
                Hours DESC
                --, Employee
        ) AS rn
    FROM tab
)
SELECT * FROM DATA WHERE rn = 1;

Answer 2

You can use the ROW_NUMBER window function, that will partition the Code column and order on the Level field (according to your priority requirement of [3,4] > [5,6] > [1,2]) and on the Hours descending.

Once you have the max amount of hours filtered out with your requirement, you can select for each row, which Hours field value corresponds to the extracted maximum value.

SELECT Employee,
       Level,
       Code,
       Hours
FROM (SELECT tab.*,
             ROW_NUMBER() OVER(
                 PARTITION BY Code
                 ORDER BY CASE WHEN Level < 3 THEN 2
                               WHEN Level > 4 THEN 1
                               ELSE                0 END, 
                          Hours DESC        ) AS HoursOrder
      FROM tab)   ordered_hours
WHERE HoursOrder = 1

Try it here.