TSQL - how to get the MAX of multiple columns

Question

I have a table with

EMPLOYEE_CODE, 
ENTITLEMENT_CODE, 
TRANS_DATE, 
UNIQUE_ID, 
HOURS.

I need to get the HOURS for the maximum TRANS_DATE for each combination of EMPLOYEE_CODE, ENTITLEMENT_CODE but it is possible there will multiple occurances of the maximum TRANS_DATE so I need to further refine to query to get the maximum of the UNIQUE_ID and hence the HOURS for this particular row that returned. I've tried various methods but I can get part of the query but not the whole thing together.

So I might have something like:

EMPLOYEE_CODE,  ENTITLEMENT_CODE,   TRANS_DATE, UNIQUE_ID,  HOURS.
-----------------------------------------------------------------
2305            ANNL                04/15/2014  28547       15.55
2305            ANNL                04/15/2014  28622       16.83
2305            ANNL                04/15/2014  28223       18.62
2305            ANNL                04/08/2014  28111       12.22
2305            SICK                04/12/2014  28150       14.47
2305            SICK                04/12/2014  28162       12.44
2305            SICK                03/11/2014  28062       18.66

So this would need to return

16.83 for 2305 ANNL 
12.44 for 2305 SICK

Any help much appreciated.

Hamlet Hakobyan · Accepted Answer · 2014-06-21 07:34:06Z

4

Try this:

;WITH CTE
AS
(
    SELECT
      *,
      ROW_NUMBER() OVER (PARTITION BY EMPLOYEE_CODE, ENTITLEMENT_CODE
                         ORDER BY TRANS_DATE DESC, UNIQUE_ID DESC) rn
    FROM tbl
)
SELECT 
  EMPLOYEE_CODE, 
  ENTITLEMENT_CODE, 
  HOURS
FROM CTE
WHERE rn = 1

SQL FIFFLE DEMO

edited Jun 21, 2014 at 7:34

answered Jun 21, 2014 at 7:27

Hamlet Hakobyan

33.5k6 gold badges55 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3762308 Over a year ago

This worked well Hamlet. I haven't used CTE before so I'll study it a bit more to improve my understanding but the query returned what I expected. Your help is appreciated.

vhadalgi · Accepted Answer · 2014-06-21 07:36:39Z

0

Try using row_number() with partition by clause

select * from
(
select *,rn=row_number()over(partition by ENTITLEMENT_CODE order by UNIQUE_ID desc)
from table
)x
where x.rn=1

edited Jun 21, 2014 at 7:36

answered Jun 21, 2014 at 7:26

vhadalgi

7,2376 gold badges44 silver badges67 bronze badges

Comments

Douglas · Accepted Answer · 2014-06-22 08:40:27Z

0

If you prefer to use a correlated subquery:

SELECT HOURS, EMPLOYEE_CODE, ENTITLEMENT_CODE
FROM mytable T1
WHERE NOT EXISTS
(
    SELECT *
    FROM mytable T2
    WHERE  T2.EMPLOYEE_CODE    = T1.EMPLOYEE_CODE
      AND  T2.ENTITLEMENT_CODE = T1.ENTITLEMENT_CODE
      AND (T2.TRANS_DATE > T1.TRANS_DATE
        OR T2.TRANS_DATE = T1.TRANS_DATE AND T2.UNIQUE_ID > T1.UNIQUE_ID)
)

You should preferably have an index over EMPLOYEE_CODE and ENTITLEMENT_CODE (compositely) for this to work efficiently.

edited Jun 22, 2014 at 8:40

answered Jun 22, 2014 at 7:02

Douglas

55.1k14 gold badges145 silver badges193 bronze badges

Comments

Vignesh Kumar A · Accepted Answer · 2014-06-21 07:40:49Z

-1

May be Something Like this

 Select T.* From Table1 T JOIN
   ( Select Max(S.UNIQUE_ID) MaxUniq,S.EMPLOYEE_CODE,S.ENTITLEMENT_CODE
     From Table1 S Join
        ( Select T1.EMPLOYEE_CODE,T1.ENTITLEMENT_CODE,Max(T1.TRANS_DATE) As MaxDate
          From Table1 T1 Group By T1.EMPLOYEE_CODE,T1.ENTITLEMENT_CODE
        ) JR On S.TRANS_DATE = JR.MaxDate AND S.EMPLOYEE_CODE = JR.EMPLOYEE_CODE AND S.ENTITLEMENT_CODE = JR.ENTITLEMENT_CODE
         Group By S.EMPLOYEE_CODE,S.ENTITLEMENT_CODE
 )R ON T.UNIQUE_ID = R.MaxUniq

Fiddle Demo

Output Would be:

EMPLOYEE_CODE   ENTITLEMENT_CODE    TRANS_DATE  UNIQUE_ID   HOURS
2305            ANNL                2014-04-15  28622       16.83
2305            SICK                2014-04-12  28162       12.44

edited Jun 21, 2014 at 7:40

answered Jun 21, 2014 at 7:35

Vignesh Kumar A

28.6k14 gold badges67 silver badges119 bronze badges

1 Comment

user3762308 Over a year ago

This worked well Vignesh and returned what I expected. I'm not sure if this has advantages over the CTE solution posted. Your help is appreciated.

Collectives™ on Stack Overflow

TSQL - how to get the MAX of multiple columns

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related