T-SQL, how to do this group by query?

According to SQL 92, if you use GROUP BY clause, then in SELECT output expression list you can only use columns mentioned in GROUP BY list, or any other columns but they must be wrapped in aggregate functions like count(), sum(), avg(), max(), min() and so on.

Some servers like MSSQL, Postgres are strict about this rule, but for example MySQL and SQLite are very relaxed and forgiving - and this is why it surprised you.

Long story short - if you want this to work in MSSQL, adhere to SQL92 requirement.

This query in SQLServer

select IDTableA, IDtableC, IDTableB, Date, Activefrom myView v1where Active = 1  AND EXISTS (              SELECT 1              FROM myView v2              group by v2.IDTableA, v2.IDTableC              Having Max(v2.Date) = v1.Date              )order by v1.IDTableA;

OR

Also in SQLServer2005+ you can use CTE with ROW_NUMBER

;WITH cte AS (                select IDTableA, IDtableC, IDTableB, [Date], Active,         ROW_NUMBER() OVER(PARTITION BY IDTableA, IDTableC ORDER BY [Date] DESC) AS rn  from myView v1  where Active = 1  )  SELECT *  FROM cte  WHERE rn = 1  ORDER BY IDTableA

Try this,

select * from table1 bwhere active = 1and date = (select max(date) from table1            where idtablea = b.idtablea            and idtablec = b.idtablec            and active = 1);

SQLFIDDLE DEMO