Sql query for max.

Question



select ca.emplid, i.current_year, i.course 
from candidate ca left outer join instructor_course_session i 
on ca.emplid = i.emplid 
where ca.EMPLOYEE_STATUS = 'A' 
and i.current_year <> 2007 
and i.SESSION_ACCEPTED_FLAG = 'Y' 
and i.DRAFTED_FLAG = 'Y' 
order by ca.emplid 
 
I have query like above and the result is like below: 
emplid   current  Course 
         _year 
1012440 2004    PMT 
1012440 2001    APTUC 
1012440 2002    PMT 
1084138 2005    AF 
123     2001    EICU 
1346653 2001    APTUC 
1924712 2004    PMT

So, as per the result above I want the result of max current_year of the emplid.

Please give me a solution for the above.

---

Divakar

Answer 1

;with cte as
(
select ca.emplid
,i.current_year
,i.course 
from candidate ca
left outer join instructor_course_session i on ca.emplid = i.emplid 
where ca.EMPLOYEE_STATUS = 'A' 
and i.current_year <> 2007 
and i.SESSION_ACCEPTED_FLAG = 'Y' 
and i.DRAFTED_FLAG = 'Y' 
order by ca.emplid 
)
select empid,
max(current_year)
from cte
group by empid

---

ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 2

Try:

;with cte as
(
select ca.emplid
,i.current_year
,i.course,
ROW_NUMBER() OVER (partition by ca.EmplID order by i.current_Year DESC) as Rn 
from candidate ca
left outer join instructor_course_session i on ca.emplid = i.emplid 
where ca.EMPLOYEE_STATUS = 'A' 
and i.current_year <> 2007 
and i.SESSION_ACCEPTED_FLAG = 'Y' 
and i.DRAFTED_FLAG = 'Y' 
order by ca.emplid 
)

select * from cte where Rn = 1

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

I am getting the result as I expect, Can you please give me this query in LINQ?

as I need to implement in LINQ only.

---

Divakar

Answer 4

I am not as proficient in LINQ - you may either try a different forum or a LinqPad. http://www.linqpad.net/

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 5

But this tool not converting SQL query to Linq.

---

Divakar