In SQL, how do I select * from a table, but when multiple rows have the same field, select only the ones where another field B is MAX?

Question

For example, I have a Table

Id	CarPartId	CarPartPrice	Metadata
1	spanner	580	Some other data
2	spanner	570	Some other data 2
3	wheel	423	Some other data
4	window	234	Some other data
5	engine	568	Some other data 1
6	engine	423	Some other data 2

Notice that when I do a SELCT * FROM this table, I would get two rows of CarPartId, but what I really want is to get the CarPartId row whereby the CarPartPrice is the highest, along with other rows from the table.

How do I achieve this? For example, my query should return this

Id	CarPartId	CarPartPrice	Metadata
1	spanner	580	Some other data
3	wheel	423	Some other data
4	window	234	Some other data
5	engine	568	Some other data 1

Answer 1

try this:

SELECT * from table INNER JOIN
  (SELECT CarPartId, MAX(CarPartPrice) as MaxPrice
    FROM table GROUP BY CarPartId
  ) grouptable
ON table.CarPartId = grouptable.CarPartId
AND table.CarPartPrice = grouptable.MaxPrice