I have the following table representing membership information:
CREATE TABLE IF NOT EXISTS `membership` ( `id` int(11) NOT NULL AUTO_INCREMENT, `organisation_id` int(11) NOT NULL, `membership_subcategory_id` int(11) NOT NULL, `start` datetime DEFAULT NULL, `end` datetime DEFAULT NULL, `amount` decimal(9,2) DEFAULT NULL, `amount_paid` decimal(9,2) DEFAULT NULL, `notes` mediumtext, `order_id` int(11) DEFAULT NULL, `payment_type` varchar(20) NOT NULL, `active` tinyint(4) NOT NULL DEFAULT '1', `cancelled` tinyint(4) NOT NULL DEFAULT '0', `cancelled_date` datetime DEFAULT NULL, `cancellation_reason` mediumtext, `certificate_sent` date DEFAULT NULL, `welcome_email_sent` date DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), UNIQUE KEY `order_id_2` (`order_id`,`start`,`end`,`organisation_id`), KEY `membership_subcategory_id_idx` (`membership_subcategory_id`), KEY `organisation_id_idx` (`organisation_id`), KEY `order_id` (`order_id`) )
- organisation_id is a member
- the membership year goes from 1 Jul to 30 Jun, start records when the membership in each year has started - this may be anywhere in the first year, but then its always 1 Jul unless a year is skipped
- membership_subcategory_id is an industry category the membership applies to for each year.
I need an efficient query to get the date joined and latest membership category.
I've tried this query, but I get "Invalid use of group function" as an error
SELECT m.organisation_id, m2.membership_subcategory_id, MIN( m.start ) FROM membership m INNER JOIN membership m2 ON m.organisation_id = m2.organisation_id WHERE MAX( m.start ) = m2.start GROUP BY m.organisation_id, m2.membership_subcategory_id