I do not understand the purpose of creating a brand new quest as the following query already fetch each employee's latest sales date.

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

Please give me the correct query, I can not proceed further..

Thanks.

wtf is the "tmp" table? where did the emp_monthly_sales table go?

why are you joining the table to itself?

please just give me the query to get each employee's latest date from the emp_monthly_sales table, so that i can show you how to build the final query that you're looking for

tmp is used instead of emp_monthly_sale as the table name is big.

I join two tables one used as a primary key table and another used as a foreign key table to fetch max date of sales.

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

Thanks

lolz, you are persistent, aren't you

okay, here's what i was looking for:latest date per employee

simple, yes?

please study it carefully and let me know when we can proceed to the second stage because we'll be using this exact query as part of it

it's simple.. thanks.

please let's proceed to the next stage..

good

next stage is to join the original table onto this query

when a query is used in the FROM clause, as a subquery, it is known as an inline view or derived table, because you can think of the results it produces as thought they were a table, which they are

so the rows would be one row per employee and the columns would be emp_id and latest date
you are joining each employee's latest date

now, as a result of the join, you can show the value for t.sales_amount, and you are now sure that it is the correct amount for the latest date

ah!! that's the trick to use that I never used in my experience..

prepared the final query and it's working fine..

thanks for your patience on me..

Follow sql can do

select * from emp_monthly_sales group by emp_id having date_time=max(date_time)

great!! It's amazing.. Thanks.

no, it is not great!!

it is not amazing, either, except in a negative way -- it is amazing how wrong it is

my query produces --hobbylu's query produces --see? wrong results!!!

What version if your ASE?
How to get the result with my sql?
Maybe you get wrong way.
I test it at 12.5.3/12.5.4/15.0.3/12.0/11.9. It always get right result.

sorry, i did not use ASE

if your query actually works and actually produces the right results, then that's great, but it says a lot about sybase

my apologies

hobbylu's query produces right result in my Sybase server as well.
my server version = ASE 12.5.2

I do not know what's wrong.
Anyway.. thanks

if it produces the right result, then it can't be wrong, can it

you might have to unlearn some techniques if you ever work with a different daabase, though

Right SQL, Right Database