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

What is your expected outcome? Do you mean you want rows sorted by `CarPartPrice`? Or you want one row with highest price? — Shireen, Nov 10 '21 at 07:21
@Shireen I do not want rows sorted by `CarPartPrice` , I simply want all the rows, but if there are multiple rows with the same `CarPartId` , I only want the rows with the highest `CarPartPrice` — Joseph N, Nov 10 '21 at 07:23
Does this answer your question? [How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?](https://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-partition-by-another-column-in-mys) — Shireen, Nov 10 '21 at 07:46

score 0 · Answer 1 · answered Nov 10 '21 at 07:20

0

Since you say "along with other rows from the table" I understand you want to see all the rows. So below will show all data but sorted with highest CarPartPrice at the top row:

Select * From this table
Order by CarPartPrice

answered Nov 10 '21 at 07:20

Erol

59
4

Shireen · Accepted Answer · 2021-11-10T07:52:12.003

0

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

edited Nov 10 '21 at 07:52

answered Nov 10 '21 at 07:32

Shireen

167
1
2
15

score 0 · Answer 3 · answered Nov 10 '21 at 07:39

I'd use a nested query.

SELECT t1.*
  FROM Table t1
 WHERE t1.CarPartPrice = ( SELECT MAX(t2.CarPartPrice)
                             FROM Table t2
                         GROUP BY t2.CarPartId
                           HAVING t2.CarPartId = t1.CarPartId)

The nested query will give you the highest CarPartPrice per Id.

score 0 · Answer 4 · answered Nov 10 '21 at 07:40

0

I think what you are looking for is select max()

SELECT CarPartId, MAX(CarPartPrice)
FROM this table 
GROUP BY CarPartId

answered Nov 10 '21 at 07:40

Dean O'Brien

335
2
11

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?

4 Answers4