Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help on max date error

Author  Topic 

02119
Starting Member

8 Posts

Posted - 2013-01-29 : 11:20:07
here is my code

select *
from dbo.vw_ComplyWise_Last_ERD
join (select EMPLOYEE_ID,MAX(DATE_OBTAINED)As Date_Obtained
from dbo.vw_ComplyWise_Last_ERD
group by EMPLOYEE_ID)
using (EMPLOYEE_ID,DATE_OBTAINED)

but i get the following error
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.

I'm trying to only return the last record based on the date obtained for each employee

Thanks i advance
Steve

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-01-29 : 11:27:16
select *
from dbo.vw_ComplyWise_Last_ERD
join (select EMPLOYEE_ID,MAX(DATE_OBTAINED)As Date_Obtained
from dbo.vw_ComplyWise_Last_ERD
group by EMPLOYEE_ID)
using (EMPLOYEE_ID,DATE_OBTAINED)

what is that? that's not valid SQL Server syntax
and you should specify what you're joining on.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-29 : 11:34:26
[code]
select t.*
from dbo.vw_ComplyWise_Last_ERD t
join (select EMPLOYEE_ID,MAX(DATE_OBTAINED)As Date_Obtained
from dbo.vw_ComplyWise_Last_ERD
group by EMPLOYEE_ID)m
ON m.EMPLOYEE_ID = t.EMPLOYEE_ID
AND m.DATE_OBTAINED = t.DATE_OBTAINED
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

02119
Starting Member

8 Posts

Posted - 2013-01-29 : 11:53:22
Thanks that worked perfectly.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-29 : 12:48:56
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -