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
 SQL Server 2000 Forums
 Transact-SQL (2000)
 MAX and Group By

Author  Topic 

nnmmss
Starting Member

3 Posts

Posted - 2012-12-08 : 08:40:27
suppose i have a table with following data in it.
Id CompanyId PersonelNo ContractDate
==============================================
1 1 10 2011/12/29
2 1 11 2011/12/29
3 2 10 2011/12/29
4 1 10 2012/06/30
5 1 10 2012/12/29
6 2 10 2012/12/29

I need to have Maximum Date for the specified Year, I mean if i want to know maximum contractDate for 2011 i should have the following result
it.
CompanyId PersonelNo ContractDate
=========================================
1 10 2011/12/29
1 11 2011/12/29
2 10 2011/12/29

and if i need to have it for year 2012 the result should be
CompanyId PersonelNo ContractDate
==========================================
1 10 2012/12/29
2 10 2012/12/29

so i did like this

SELECT DISTINCT TOP 100 PERCENT MAX(ContractDate) AS ContractDate, PersonelNo, CompanyId
FROM dbo.Contract
GROUP BY PersonelNo, CompanyId , ContractDate
HAVING (ContractDateLIKE '2012%')

the result would be
CompanyId PersonelNo ContractDate
==========================================
1 10 2012/06/30
1 10 2012/12/29
2 10 2012/12/29

how should write the query?

thank you

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-08 : 09:31:57
Remove ContractDate column from the group by clause
SELECT DISTINCT TOP 100 PERCENT MAX(ContractDate) AS ContractDate, PersonelNo, CompanyId 
FROM dbo.Contract
GROUP BY PersonelNo, CompanyId
HAVING (ContractDate LIKE '2012%')


From the way you have written the query, it looks like Contract Date is a character type column. It would be much better to have DATETIME as the data type for that.
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-08 : 13:11:46
[code]
Select C.* from Contract C
inner join
(
Select CompanyId,PersonelNo,MAX(ContractDate) ContractDate
from dbo.Contract
Group by CompanyId,PersonelNo
)CT.CompanyId = C.CompanyId and CT.PersonelNo = C.PersonelNo
and CT.ContractDate = C.ContractDate
Where Year(ContractDate) = 2012[/code]
Go to Top of Page
   

- Advertisement -