Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
It would help if you added a few sample rows from the table and what you want as result. –  ypercube Mar 30 '13 at 8:39
    
Is the table using MyISAM or InnoDB engine? –  ypercube Mar 30 '13 at 11:41
    
InnoDB engine..... –  automatem Mar 31 '13 at 6:05
add comment

1 Answer

Not sure if I understand your requirements correctly. This query will return, for every organization, the minimum start date and the latest membership_subcategory_id (when the order is by start):

SELECT    organisation_id, 
            MIN(`start`) AS 
          first_start_date,
            ( SELECT    m2.membership_subcategory_id
              FROM      membership AS m2
              WHERE     m2.organisation_id = m.organisation_id
              ORDER BY  m2.`start` DESC
                LIMIT 1
            ) AS 
          last_membership_subcategory_id
FROM      membership AS m
GROUP BY  organisation_id ;

The subquery will be executed as many times as the number of different organizations in the table, so efficiency depends on that. An index on (organisation_id, start, membership_subcategory_id) would help minimizing the subquery execution time.


Using a derived table:

SELECT    gm.organisation_id, 
          gm.first_start_date,
          m.membership_subcategory_id AS last_membership_subcategory_id,
          m.*                           --- whatever other data you want
                                        --- from the latest `start` date
FROM      ( SELECT    organisation_id, 
                      MIN(`start`) AS first_start_date,
                      MAX(`start`) AS last_start_date
            FROM      membership
            GROUP BY  organisation_id 
          ) AS gm 
    JOIN
          membership AS m
              ON  m.organisation_id = gm.organisation_id
              AND m.`start` = gm.last_start_date
  ;
share|improve this answer
    
Thanks, I've come to this conclusion as well. I wondered if there is a way to just use group by and joining the table again. The question is of course will this be faster. –  automatem Mar 30 '13 at 9:18
    
Yes, you can. If you need more columns from the "latest" row, a derived table with group-by would probably be more efficient (or you would need a more wide index, that would include all the needed columns). –  ypercube Mar 30 '13 at 9:39
    
Thats what I tried in my original query, can you advise how to link max(start) with the derived table? –  automatem Mar 30 '13 at 9:47
    
fantastic! I have to wait 10 hours to award the bounty –  automatem Mar 30 '13 at 20:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.