Re: Select first record of each ID mysql5.6
Posted by:
Dave F
Date: December 17, 2020 12:43PM
I need one row per foreign_id in the stivagallery_plugin_gallery table (which equates to id in the stivagallery_galleries table).
CREATE TABLE `stivagallery_galleries` (
`fuel` varchar(255) NOT NULL,
`model` varchar(255) NOT NULL,
`make` varchar(255) NOT NULL,
`engine` varchar(255) NOT NULL,
`hp` smallint(6) NOT NULL,
`drive` varchar(255) NOT NULL,
`title` varchar(255) NOT NULL,
`story` longtext NOT NULL,
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`layout` int(10) unsigned DEFAULT '1',
`target` enum('_blank','_self') DEFAULT '_self',
`width` smallint(5) unsigned DEFAULT '320',
`height` smallint(5) unsigned DEFAULT '240',
`preview_width` smallint(5) unsigned DEFAULT '320',
`preview_height` smallint(5) unsigned DEFAULT '240',
`thumb_width` smallint(5) unsigned DEFAULT '100',
`thumb_height` smallint(5) unsigned DEFAULT '75',
`ratio` varchar(50) DEFAULT '1_1',
`custom_width` smallint(5) DEFAULT '1',
`custom_height` varchar(50) DEFAULT '1',
`max_width` varchar(50) DEFAULT '1000',
`slideshow_speed` int(10) DEFAULT '1500',
`created` datetime NOT NULL,
`status` enum('T','F') NOT NULL DEFAULT 'T',
PRIMARY KEY (`id`),
KEY `layout` (`layout`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
CREATE TABLE `stivagallery_plugin_gallery` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`foreign_id` int(10) unsigned DEFAULT NULL,
`model` varchar(80) DEFAULT NULL,
`hash` varchar(32) DEFAULT NULL,
`mime_type` varchar(255) DEFAULT NULL,
`small_path` varchar(255) DEFAULT NULL,
`small_size` int(10) unsigned DEFAULT NULL,
`small_width` smallint(5) unsigned DEFAULT NULL,
`small_height` smallint(5) unsigned DEFAULT NULL,
`medium_path` varchar(255) DEFAULT NULL,
`medium_size` int(10) unsigned DEFAULT NULL,
`medium_width` smallint(5) unsigned DEFAULT NULL,
`medium_height` smallint(5) unsigned DEFAULT NULL,
`large_path` varchar(255) DEFAULT NULL,
`large_size` int(10) unsigned DEFAULT NULL,
`large_width` smallint(5) unsigned DEFAULT NULL,
`large_height` smallint(5) unsigned DEFAULT NULL,
`source_path` varchar(255) DEFAULT NULL,
`source_size` int(10) unsigned DEFAULT NULL,
`source_width` smallint(5) unsigned DEFAULT NULL,
`source_height` smallint(5) unsigned DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`title` varchar(255) DEFAULT NULL,
`main_title` varchar(255) NOT NULL,
`story` longtext NOT NULL,
`description` text,
`alt` varchar(255) DEFAULT NULL,
`watermark` varchar(255) DEFAULT NULL,
`sort` int(10) unsigned DEFAULT NULL,
`created` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `foreign_id` (`foreign_id`),
KEY `hash` (`hash`),
KEY `model` (`model`)
) ENGINE=InnoDB AUTO_INCREMENT=80 DEFAULT CHARSET=utf8