Simple query to get group results

Question

Hi how can i get only unly unique depts from the below example?

Dept Id Created Date

06013cd7-2224-4220-b048-a54bbd1ff403 2009-09-08 17:36:11.293

06013cd7-2224-4220-b048-a54bbd1ff403 2009-09-08 17:41:54.857

5e29bd98-04ba-452d-bfcd-caa63ab9018b 2009-09-08 17:20:45.373

Actually i tried like this

select top 10 deptid, (Select convert(varchar,createddate,101)) from depts where [status]='Y' group by deptid,convert(varchar,createddate,101)

but is showing all results. But i want like this

Dept Id Created Date

06013cd7-2224-4220-b048-a54bbd1ff403 2009-09-08

5e29bd98-04ba-452d-bfcd-caa63ab9018b 2009-09-08

Can you help me to write this query

Thank you

Answer 1

SELECT deptid, MAX(createdate) FROM depts WHERE [status] = 'Y' GROUP BY deptid

Answer 2

You're returning all rows because you're including the date in the grouping. Try:

select deptid, Max(convert(varchar,createddate,101)) AS MaxDate
from depts 
where [status]='Y' 
group by deptid