GROUP BY Question

Hi, I am trying to group the records by there games.name but it isnt working, I know in mysql you just use GROUP BY but in sql server its different I guess.

A detail explanation would be instead of

CheatID GameID Title Game Console
1 1 Test NFS PC
2 1 Test NFS PC
3 2 Test NFS2 PS2

I need to do this

CheatID GameID Title Game Console
1 1 Test NFS PC
3 2 Test NFS2 PS2

SELECT TOP 5
Cheats.CheatID,
Cheats.GameID,
Cheats.Title,
Cheats.Added,
Games.Name Game,
Consoles.Name Console
FROM Cheats
INNER JOIN Games ON (Games.GameID = Cheats.GameID)
INNER JOIN Consoles ON (Consoles.ConsoleID = Games.ConsoleID)
GROUP BY Games.Name
ORDER BY Cheats.Added DESC

Does anyone know a solution to this?

Thanks, Mike

You need to tell in SQL what to do with the CheatID. I'm surprised that the code you have even compiles, since you are using group expressions on some columns and not others.

Something like this (I removed the Cheats.GameID since you don't need it in the select statement):

SELECT TOP 5
MAX(Cheats.CheatID),
Cheats.Title,
Cheats.Added,
Games.Name Game,
Consoles.Name Console
FROM Cheats
INNER JOIN Games ON (Games.GameID = Cheats.GameID)
INNER JOIN Consoles ON (Consoles.ConsoleID = Games.ConsoleID)
GROUP BY Games.Name
ORDER BY Cheats.Added DESC

You might even want to change the MAX to a COUNT of Cheats.CheatID, then in the gridview you can display the number of cheats for the game on a given console rather than an arbitrary cheat ID.|||

No it doesn't compile. that was my problem. and your solution will not compile either.

I need to select mainly the gameid, title, games.name and consoles.name and group by the games.name and order by the cheats.added

thanks, mike

|||no one knows a solution to this?