SEO

select ano,max(date),a_subject from MY_TAB where table_name='xyz' and
ano=877 group by a_subject,ano order by a_subject

id max(Date) A_Subject
877 2005-01-20 00:00:00.000 Subject_1
877 1900-01-01 00:00:00.000 Subject_2
877 2004-12-20 00:00:00.000 Subject_3
877 2005-01-19 00:00:00.000 Subject_4

--------------------------------------------------------------------------
When I put the status column in, it fetches all the rows.

select ano,max(date),a_subject,status from MY_TAB where
table_name='xyz' and ano=877 group by a_subject,ano,status order by
a_subject

ANO max(Date) A_Subject Status
877 2005-01-20 00:00:00.000 Subject_1 Not
Started
877 1900-01-01 00:00:00.000 Subject_2 Not
Started
877 2004-12-20 00:00:00.000 Subject_3 Completed
877 1900-01-01 00:00:00.000 Subject_3 Not
Started
877 1900-01-01 00:00:00.000 Subject_4 Not
Started
877 2005-01-19 00:00:00.000 Subject_4 Not
Started

-----------------------------------------------------------------------
now what i want is

ANO max(Date) A_Subject Status
877 2005-01-20 00:00:00.000 Subject_1 Not
Started
877 1900-01-01 00:00:00.000 Subject_2 Not
Started
877 2004-12-20 00:00:00.000 Subject_3 Completed
877 2005-01-19 00:00:00.000 Subject_4 Not
Started
-----------------------------------------------------------------------------
This is one of the suggestions by Erland

SELECT a.ano, a.a_subject, b.status
FROM (select ano, max(date), a_subject
from MY_TAB
where table_name='xyz' and ano=877
group by a_subject, ano) AS b
JOIN MY_TAB b ON a.ano = b.ano
AND a.maxdate = b.date
AND a.a_subject = b.a_subject

but I am getting this error

Server: Msg 1011, Level 15, State 1, Line 6
The correlation name 'b' is specified multiple times in a FROM clause.
Any ideas?

Thanks

This line:
group by a_subject, ano) AS b
should be:
group by a_subject, ano) AS a