MAX from grouping 1

[micang]

SQL 2005

Hi All,

I have a table with the following data:

ID,Make
1,Ford
2,Ford
3,Ferrari
4,Ferrari
5,Ferrari
6,Ferrari
7,Mazda
8,Renault
9,Renault
10,Renault

ID column is a primary key.

The result I world ideally like from the above is:

ID,Make
2,Ford
6,Ferrari
7,Mazda
10,Renault



I have tried with the code below, but this returns all the ID's.

SELECT     MAX(ID) AS Expr1, make
FROM         table1
GROUP BY ID, Make

Can't seem to think how to go about this, I am sure it's probably simple enough, I just can't see it, yet.

Any help in the correct direction will be appreciated.

Thank you
Michael

 

[RiverGuy]

You want to AGGREGATE one column, GROUPING BY another. So your query needs to look like this:

SELECT     MAX(ID) AS Expr1, make
FROM         table1
GROUP BY Make

 

[micang]

Thanks RiverGuy, silly me, grouping by ID (primary key), obviously that will return all the records.

Appreciate your time.

Michael