CODEWITHSUNDEEP
sql-servert-sqlmaxmultiple-columns
user3762308
user3762308

Reputation: 21

TSQL - how to get the MAX of multiple columns

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.

Upvotes: 2

Views: 108

Answers (4)

Douglas
Douglas

Reputation: 54887

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.

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

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

Upvotes: -1

vhadalgi
vhadalgi

Reputation: 7189

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

Upvotes: 0

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

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

Upvotes: 4

Related Questions