getting value from max date

[SqlHunter]

Here I have a table called tbl_items


item_id emp_id date amount
1 2345 12/12/2004 100
4 2345 01/01/2005 200
5 4567 03/03/2005 500
7 4567 05/05/2005 NULL


Please somebody help me with this query.I need to get the amount for the max date of the same emp_ids
say result shoould be

for 2345 200
for 4567 NULL

 

[jhall01]

SELECT emp_id, amount
FROM
  tbl_items a
  JOIN
  (
  SELECT emp_id, max(date) max_date FROM tbl_items GROUP BY emp_id
  ) b
  ON
  a.emp_id = b.emp_id
  and a.date = b.max_date

 

[Pattycake245]

Tested and works

select emp_id, amount from tbl_items t
where [date] =
(select max([date]) from tbl_items where emp_id=t.emp_id)
group by emp_id, amount

I hope that the field "date" is just an example and not your actual name. It is a reserved word and should be changed to something more descriptive.

If you want to return a "0" instead of NULL just put isnull around the amount.

Tim