MySQL query not quite right...probably pretty simple

I've got a table for a payroll system, with four fields, and some sample data:

pkref  employee_id  new_wage  effective_date
=====  ===========  ========  ==============
23     06031-BOB    10        080101
37     06031-BOB    15        090501
90     06031-BOB    13        110228 

When an employee's wage is changed, a primary key reference auto-increments, and the appropriate informatio开发者_如何学JAVAn is recorded (effective_date is a timestamp, yymmdd). No problems there.

Now, I'm trying to get a query to find

1. All entries related to an employee, then
2. The maximum effective_date stamp in those sub-entries
3. The wage that corresponds to that maximum.

I've made my very first subquery ever (!), got it almost right, but it's buggy. Could some guru have a look and give me a bump in the right direction?

SELECT MAX(effective_date),new_wage FROM (SELECT effective_date,new_wage FROM hr_wages WHERE employee_code='06031-BOB') AS t1

Ideally, I want 110228 and 13 to be returned. But, as the aforementioned guru will no doubt see immediately, something is wrong. The new_wage value does not always match the max effective_date.

So. Vat to do?

---

Hang on, what's stopping you from doing the following?

select effective_date, new_wage from hr_wages
where employee_code = '06031-BOB'
order by effective_date desc
limit 1

---

For only one employee you can use IN with subquery

   SELECT new_wage, other data... 
    FROM hr_wages
    WHERE effective_date IN (SELECT max(effective_date) FROM hr_wages WHERE employee_code='06031-BOB')
AND employee_code='06031-BOB' -- Corrected: Credits to Stev

e

For all employees

SELECT new_wage, CO 
FROM hr_wages w LEFT JOIN
(SELECT max(effective_date) effective_date, employee_code 
FROM hr_wages 
GROUP BY employee_code
)d ON w.employee_code = d.employee.code AND w.effective_date = d.effective_date

---

There was a post from @niktrs that provided the right answer, but it's been deleted!

So, here's the query I was looking for, with @niktrs help. If his post comes back, I'll accept it.

SELECT new_wage FROM hr_wages WHERE effective_date IN 
(SELECT effective_date,new_wage FROM hr_wages  WHERE employee_code='06031-BOB') 
AND employee_code='06031-BOB'

This ugliness can be beautified, I'm sure.