Get the distinct records for the maximum value

Question

I have a query like this:

SELECT distinct(A.EMP_ID), C.MAINT_ID 
FROM EMPLOYEE_MASTER A   
LEFT OUTER JOIN DESIGNATION_MAINTENANCE C ON A.EMP_ID = C.EMP_ID

Which returns the following output.

EMP_ID  |MAINT_ID
----------------
15       NULL
16       NULL
17       NULL
18       1
18       2
18       3
19      NULL
20      NULL
21      4
21      5
22      NULL
23      NULL

Now the actual result what I need is

Get the Max of maint_id for the Emp _id and display only the max record.

For example for Emp_id 18 there are three records. But i need the max one, i.e maint_id =3

So the output that I expected is like

EMP_ID  MAINT_ID
------------------    
15  NULL
16  NULL
17  NULL
18  3
19  NULL
20  NULL
21  5
22  NULL
23  NULL

What I have tried so far is

SELECT 
    (A.EMP_ID), C.MAINT_ID 
FROM 
    EMPLOYEE_MASTER A   
LEFT OUTER JOIN 
    DESIGNATION_MAINTENANCE C ON A.EMP_ID = C.EMP_ID 
                              AND C.MAINT_ID = (SELECT TOP(1) MAINT_ID 
                                                FROM DESIGNATION_MAINTENANCE   
                                                ORDER BY MAINT_ID DESC)

which returns:

EMP_ID  MAINT_ID
------------------
15  NULL
16  NULL
17  NULL
18  NULL
19  NULL
20  NULL
21  5
22  NULL
23  NULL

Which is not my expectation. How to do it? Any help appreciated

Isn't this just `A.EMP_ID, MAX(C.MAINT_ID) FROM ... GROUP BY EMP_ID`? — Damien_The_Unbeliever, Dec 30 '15 at 09:17
Possible duplicate of [how to get the distinct records based on maximum date?](http://stackoverflow.com/questions/23336663/how-to-get-the-distinct-records-based-on-maximum-date) — Werner Henze, Dec 30 '15 at 09:19

Stanislovas Kalašnikovas · Answer 1 · 2015-12-30T09:41:22.093

1

You can simple use MAX and add GROUP BY clause in following:

SELECT (A.EMP_ID)
       ,MAX(C.MAINT_ID ) MAINT_ID
FROM EMPLOYEE_MASTER A   
LEFT OUTER JOIN DESIGNATION_MAINTENANCE C ON A.EMP_ID = C.EMP_ID 
GROUP BY A.EMP_ID

Or if you want to use this poor solution with subquery, try in following:

SELECT (A.EMP_ID)
       ,C.MAINT_ID 
FROM EMPLOYEE_MASTER A   
LEFT OUTER JOIN DESIGNATION_MAINTENANCE C ON A.EMP_ID = C.EMP_ID AND 
                C.MAINT_ID =(select top(1) MAINT_ID from DESIGNATION_MAINTENANCE C1 WHERE C.EMP_ID = C1.EMP_ID ORDER BY MAINT_ID DESC)

edited Dec 30 '15 at 09:41

answered Dec 30 '15 at 09:18

Stanislovas Kalašnikovas

9,203
4
32
59

Actually i have posted a piece of query. There are so many columns so using group by might not solve my issue. – Shreyas Achar Dec 30 '15 at 09:28
@ShreyasBhandimatt - please edit your question then and provide a *realistic* example and expected results. We're not mind readers. – Damien_The_Unbeliever Dec 30 '15 at 09:36

score 0 · Answer 2 · answered Dec 30 '15 at 09:20

Try this :

SELECT (A.EMP_ID)
  ,C.MAINT_ID FROM 
                 EMPLOYEE_MASTER A LEFT OUTER JOIN 
(SELECT EMP_ID, MAX(MAINT_ID) AS MAINT_ID FROM DESIGNATION_MAINTENANCE GROUP          BY EMP_ID) C 
                 ON A.EMP_ID = C.EMP_ID 
                 ;

Get the distinct records for the maximum value

2 Answers2