Hi,

I have a question regarding filtering records. Kindly please help.
I have the following table.

Table: emp_monthly_sales
emp_id|date_time|sales_amount
101|20100105|250000
102|20100118|750000
101|20100409|1800000

Now I just want an SQL query to pick records of each employee's latest sales.
So the query result should be like the following.

102|20100118|750000
101|20100409|1800000

Here emp_id is the foreign key.

Thank you.

Regards,
Sakitram

Why on earth would we want to do your coursework?

Hi, it's not my coursewoork, but in just asked a way how to do a task in database in my profession. Please help.

sure, be glad to

we'll do it in two stages

first, please write a query that returns the latest date for each employee

SELECT max(date_time) FROM emp_monthly_sales

that's actually the latest date for all employees.

SELECT max(date_time) FROM emp_monthly_sales group by emp_id.

I think the answer is the following.

SELECT a.emp_id, max(b.date_time), b.sales_amount
FROM emp_monthly_sales a, emp_monthly_sales b
WHERE a.emp_id=b.emp_id
GROUP BY a.emp_id

I'm not sure.

The simple way to see if you're correct is to run the query.
Do you get the latest date for each employee?

this is syntactically correct, but not semantically

it does give you a result set of dates, and they do happen to be the last date for each employee, however, all you get is the dates... so this query has a very serious logical shortcoming -- you can't tell whose dates they are!!

sorry... that did not give result what I wanted..
please correct the query.. thanks.

how about if i teach you how to fix it

let's do this in two stages

first, please write a query that returns the latest date for each employee

here is the query

SELECT a.emp_id, max(b.dt)
FROM tmp a, tmp b
WHERE a.emp_id=b.emp_id
GROUP BY a.emp_id

now we're making progress

before going to stage 2, i wonder if i could ask you to please rewrite this query using JOIN syntax instead of the implicit comma join you have now

also, would you please assign a column alias to the MAX expression

SELECT a.emp_id, max(b.dt) sales_date
FROM tmp a left join tmp b
on a.emp_id=b.emp_id
GROUP BY a.emp_id

my sincere apologies, i was so anxious to move on with this that i only briefly examined your last query, and sort of automatically asked you to rewrite the join (because i won't work with anything except explicit JOIN syntax)

but now i need for you to go back to square one, as it were, and write a brand new query that gets the latest sales date for each employee...

... except that there will be only one table involved in the query, namely, the single table that you talked about in your first post