Re: Select first record of each ID mysql5.6

[Peter Brawley]

SELECT
  t1.id,
  t1.foreign_id,
  t1.sort,
  t1.small_path,
  t2.title
FROM stivagallery_plugin_gallery t1
JOIN stivagallery_galleries      t2 ON t1.foreign_id = t2.id
JOIN (                 -- LIMIT TO MIN SORT PER FOREIGN_ID
  select foreign_id, min(sort) as minsort
  from stivagallery_plugin_gallery
  group by foreign_id
) s on t1.foreign_id=s.foreign_id and t1.sort=s.minsort
ORDER BY t1.foreign_id ;
+----+------------+------+-------------------------------------------------------------+------------------------+
| id | foreign_id | sort | small_path                                                  | title                  |
+----+------------+------+-------------------------------------------------------------+------------------------+
| 10 |          1 |    1 | app/web/upload/small/1_4d3e9a7afbf229c316706680f7cd65f6.jpg | Haddock's Revenge |
|  6 |          2 |    2 | app/web/upload/small/2_b4972e9778a5f9b34437c358a002d978.jpg | Doggy piccies.         |
|  7 |          3 |    1 | app/web/upload/small/3_977a27522a2887f0f5cdfc14f23333a3.jpg | Snowbird pics.         |
| 32 |          4 |    1 | app/web/upload/small/4_970ac841e739f5c7bbf4b7dd47187715.jpg | Leo Magill             |
| 71 |          5 |    1 | app/web/upload/small/5_6b84c11375ef2a85b0396d4f2512a02a.jpg | Snowbird               |
| 76 |          7 |    1 | app/web/upload/small/7_771451f305edb9687ba86785fc95f99b.jpg | ERAN                   |
| 78 |          9 |    1 | app/web/upload/small/9_3412b4f19054a66c0ae6f6fc45271a3c.jpg | Summer Breeze          |
+----+------------+------+-------------------------------------------------------------+------------------------+

Edited 2 time(s). Last edit at 12/17/2020 02:00PM by Peter Brawley.