Problem with MSSQL

It works in MySQL

[Timotheos]

I've been working with Microsoft SQL Server recently and ran into this problem. I'm basically building a software tracker and I want to grab the latest rev of each software and it's corresponding data. So I use this select statement.

SELECT software_number, max(revision) as revision, models FROM software GROUP BY software_number

It gives me this error.

Warning: mssql_query(): message: Column 'software.models' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (severity 16) in c:\inetpub\wwwroot\softorg\item_assign.php on line 149

The frustrating thing is that this very same select works great in MySQL.

Any suggestions on what the problem is and how I could do this in MSSQL?

[digitalv]

Change GROUP BY to ORDER BY unless you specifically need group by. If you do need it you would have to do a group by clause of GROUP BY software_number,max(revision),models

Since I'm not really sure what you're trying to do (what the fields are, the way you want the results, etc.) you may need a "HAVING" clause in there if group by is required over order by.

[Timotheos]

Here's an example of the table...

ID, SW#, Revision, Models
1, 1200, A, PS7-MGT100~200
2, 1200, B, PS7-MGT100~300
3, 1200, C, PS7-MGT100~300 <<<(I want this one)
4, 1274, A, PS8-MGT15 <<<<<<<<(this one)
5, 1284, A, PS9-MGT15
6, 1284, B, PS9-MGT15~200 <<<<(and this one)

[coopster]

Do any of the columns have user-defined data types? Or I guess more importantly, which version of MSSQL is it, and what is the latest level of service packs? There are a boatload of bug fixes for MSSQL aggregate functions. Query the M$ support knowledge base for details...