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 |

Some pictures are available in three resolutions (1280, 800, 600), and some only in the minimal (600).

Question: item_id resolution for each item_id ?

============= IN RUSSIAN ==============
As you can see, some images are available in three resolutions (1280, 800, 600), and some only in the minimum resolution (600).

Question: How to create a request that would select one image with the highest possible resolution for each position?

  • It is somehow strange that for the same item with different resolutions the same image ID. Not exactly plowed with sample data? - Akina

1 answer 1

Good day. Here is a solution to this problem:

 SELECT `item_id`, `image_id`, MAX(`dimensions`) FROM `item_images` GROUP BY `image_id`