>Given this data:
>ITEM DESCRIPT STYLE
>aaa Item aaa 1
>bbb Item bbb 1
>ccc Item ccc 2
>ddd Item ddd 3
>eee Item eee 2
>
>A pre-VFP 8.0 SQL statement such as:
>SELECT itemno, descript, styleid, count(item) AS count
> GROUP BY styleid ;
> ORDER BY styleid ;
> INTO CURSOR xxx
>
> You get the following result:
>ITEM DESCRIPT STYLE COUNT
>bbb Item bbb 1 2
>eee Item eee 2 2
>ddd Item ddd 3 1
>
>But this won't work in MS SQL Server. It generates an error "Invalid Group By -- all select fields must be used in an aggregate function."
>
>So if I wantto simulate the result of the above VFP SQL statement in MS SQL Server, how do I do that? What combination of SQL Statements or computed columns or whatever do I need??
>
>Joel

select T.ItemNo, T.Descript, T.StyleID, D.cntItems
from myTable T inner join
(select StyleID, count(Item) as cntItems from myTable group by StyleID) D
on T.StyleID = D.StyleID

Although, you're using max for the Item and Descript, so, SQL Server 2005 and up:

;with cte as (select *, 
row_number() over (partition by StyleID order by Item desc) as Row,
 count(Item) over (partition by StyleID) as [Count] from Styles)

select * from cte where Row = 1