CODEWITHSUNDEEP
mysql
user527892
user527892

Reputation:

MySQL query - get row with highest number filtered by another column

I have a table with data similar to this:

id  job_id  filename    status  revision    product
----------------------------------------------------------
1   1       1r0.jpg     P       0           Product 1
2   1       2r0.jpg     P       0           Product 2
3   1       3r0.jpg     P       0           Product 3
4   1       2r1.jpg     P       1           Product 2

I want to run an SQL query that returns the following:

id  job_id  filename    status  revision    product
----------------------------------------------------------
1   1       1r0.jpg     P       0           Product 1
3   1       3r0.jpg     P       0           Product 3
4   1       2r1.jpg     P       1           Product 2

i.e. if there's two rows with the same product name, I want to get the one with the highest revision number.

I've tried lots of queries using groupby, max, distinct to no avail. I've tried self-joins but cannot get the results I'm after.

For example, the query SELECT *, MAX(revision) FROM artworks GROUP BY product gives me data that looks fine at first glance but the row contains the data from the one with the lowest revision number, not the highest.

Upvotes: 2

Views: 69

Answers (2)

Mohsen Heydari
Mohsen Heydari

Reputation: 7284

You may get the same result without a sub-query:

SELECT a.*
FROM artworks a
left join artworks b
on a.produc = b.produc
and a.revision < b.revision
where b.id is null;

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726599

You can do it without GROUP BY, like this:

SELECT *
FROM artworks a
WHERE NOT EXISTS (
    SELECT *
    FROM artworks aa
    WHERE a.product=aa.product AND a.revision < aa.revision
)

Upvotes: 1

Related Questions