-2

I have a simple database:

mysql> select item_id, image_id, dimensions from item_images order by item_id;

+---------+-----------+------------+
| item_id | image_id  | dimensions |
+---------+-----------+------------+
|  810484 | 456239042 |        600 |
|  810484 | 456239042 |        800 |
|  810484 | 456239042 |       1280 |
|  810488 | 456239043 |        800 |
|  810488 | 456239043 |       1280 |
|  810488 | 456239043 |        600 |
| 1582704 | 456239313 |        600 |
| 1582719 | 456239314 |        600 |
| 1582733 | 456239315 |        600 |

As you can see, some pictures are available in three resolutions (1280, 800, 600), and some only in the minimal (600).

Question: How to compose a query that would select one picture with the highest possible resolution for each item_id?

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Vitaly
  • 1
  • 1

1 Answers1

0

Hi i did little research and i found what your looking for right here https://stackoverflow.com/a/12102288/4771198

joulani
  • 23
  • 7
  • Hi, By means of experiments I came to the following request: `SELECT item_id, image_id, album_id, MAX (dimension) from item_images GROUP BY item_id ORDER BY item_id` It works quickly and gives out exactly what I need :) – Vitaly Mar 29 '18 at 11:09