select distinct account ?

[stonehead]

Hi all,
My table has fields:
account,agentID, date, conclusion_code

with duplicate account exist now I need to create another table with distinct account and choose other values based on the latest date

This is what I tried

CREATE TABLE #UniqueAccount2 (
Account VARCHAR(50),
Agent int,
Conclusion_cd Varchar(2),
)

INSERT INTO #UniqueAccount2
SELECT DISTINCT Account, agent, conclusion_cd
FROM dbo.tbl_mainTable
group by Account
where date = max(date)

it doesnt work.
error msg: agent is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Please help me to fix it. Thanks in advance

 

[Qik3Coder]

your overlapping yourself with the distinct and the group by. Pick one or the other. The way you are using it you get the same result.

To fix your error, either take the group by off, or add the other two columns (and remove the distinct)

-The answer to your problem may not be the answer to your question.

 

[stonehead]

I tried what you suggested and I still got duplicate account. I need to add 'date' condition to my select statement to pick up only the latest values. How do I do that ? WHERE/HAVING date=max(date) doesn't work. Any idea ? Thanks a lot.

SELECT Account, agent, conclusion_cd
FROM dbo.tbl_hearingStats_Production
GROUP BY ACCOUNT, agent, conclusion_cd
--having h_date = max(h_date)

 

[Qik3Coder]

You may have gotten a duplicate account number but some other piece of data is not duplicated, which is why you got the second row. You need to decide what data you want to show, because that will determine whether or not you have dupes.

-The answer to your problem may not be the answer to your question.

 

[stonehead]

I understand. That's why I said I just need other data of the lastest date/time. Distinct account# is a must for me. thank you

 

[Qik3Coder]

i think you need the h_date in the select list if you want to use it as part of the the "having", which means you need to have it in the groupby

-The answer to your problem may not be the answer to your question.

 

[stonehead]

Thanks a lot for your help. I got it works already.

 

[inyama]

can you post how you got it to work? i am having the same difficulty and i need to use min instead of max to no success.

Thanks.

Goz.

 

[djj55]

Hello,
how about something like:

select account, agentID, date, conclusion_code 
from maintable 
inner join (select account, max(date) as mdate from maintable) jt
ON maintable.account = jt.account and maintable.date = jt.mdate

This is not tested but it gives you the idea.
djj