find value based on max(date)

I know I have done this before, but cannot for the life of me remember how.

I am trying to determine return the current (last added) deduction amount for each deduction type for each employee

Sample Table:
employee|Deduction_type|Date_entered|Amount
1|MED|1/1/2007|50
1|DEPC|1/1/2007|100
1|MED|1/8/2007|50
1|DEPC|1/8/2007|100
1|MED|1/15/2007|150
2|MED|1/1/2007|35
2|DEPC|1/1/2007|100
2|MED|1/8/2007|35
2|DEPC|1/8/2007|75
2|MED|1/15/2007|35

Any suggestions?select t.employee
, t.Deduction_type
, t.Date_entered
, t.Amount
from Sample as t
inner
join (
select employee
, Deduction_type
, max(Date_entered) as max_date
from Sample
group
by employee
, Deduction_type
) as m
on m.employee = t.employee
and m.Deduction_type = t.Deduction_type
and m.max_date = t.Date_enteredsql

Find Partial Text From Return Of Subquery

I don't even know if this is even possible but I figured it'd make my life a lot easier if it was. I have an organizational table with departments and subdepartments that has a chain of command listing in it. (I didn't design it, don't shoot me, I'm just having to fix it). What I need to do is find a department and all departments beneath it based on the passed in value of the department's ID. I'm right now keying off the chain of command since the chain of command is from the top down.

SELECT *
FROM Department
WHERE (COC LIKE
(SELECT COC
FROM Department
WHERE DeptID = '12345'))

Though of course this doesn't work. It will only return those identical to the returned value. If I were to just "do it" without a subquery it'd be
SELECT * FROM Department WHERE COC LIKE '1;14;16;232;12345;' and it would return everything from this department downward.

Is there a way to do a partial like on a subquery results?Not sure if I understood you correctly but try this:

Select *
from Department a
join Department b on a.COC = b.DeptID
where a.DeptID = '12345'