MAX and Group By - SQL Server Forums

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.

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]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources