title: MySQL Find Most Recent/Largest Record Per Group by order by
body: <p>MySQL Find Most Recent/Largest Record Per Group by order by, and how can i minimize / shorten  this query and every time it returns the first row  value of the group, whereas i like to select the last row value of a group, and sort the values based on <code>ja.id</code> ? I know this is a bad query can anyone suggest or provide me solution to shorten this query . I have used all the necessary Column Indexes in all the tables. <strong><em>How to shorten the query without the use of union all .both the queries in union all are same expect in the where statement.</em></strong></p>
  
  <pre><code>SELECT
  a.previous_status,
  a.rejected_status,
  a.rejection_reason_text,
  a.rejection_reason,
  a.rjaId,
  a.refer_applied_status,
  a.title,
  a.playerId,
  a.gameId,
  a.gamePostDate,
  a.game_referal_amount,
  a.country,
  a.country_name,
  a.state,
  a.location,
  a.state_abb,
  a.game_type,
  a.appliedId,
  a.appliedStatus,
  a.admin_review,
  a.is_req_referal_check,
  a.memberId,
  a.appliedEmail,
  a.game_id,
  a.referred_id,
  a.memStateAbb,
  a.memState,
  a.memZipcode,
  a.memCity,
  a.memCountryNme,
  a.memCountry,
  a.appliedMemberName,
  a.first_name,
  a.primary_contact,
  a.last_name,
  a.addressbookImage,
  a.userImage,
  a.last_login,
  a.user_experience_year,
  a.user_experience_month,
  a.current_designation,
  a.current_player,
  a.appliedDate,
  a.addressbook_id,
  a.joiningdate,
  a.gameStatus,
  a.gameReferalAmountType,
  a.gameFreezeStatus,
  a.gameFreezeMsg,
  a.app_assign_back_to_rp_count,
  a.applied_source,
  a.max_id,
  a.gamesApplied,
  a.gamesAppliedId,
  SUM(a.totalgameApplied) AS totalgameApplied,
  a.application_assign_to_rp_status,
  a.rpAppliedSource,
  a.applied_on
                  FROM
  (
      (
          SELECT
              ja.previous_status,
              ja.rejected_status,
              ja.rejection_reason_text,
              ja.rejection_reason,
              rja.id AS rjaId,
              rja. STATUS AS refer_applied_status,
              jp.title,
              jp.user_user_id AS playerId,
              jp.id AS gameId,
              jp.posted_on AS gamePostDate,
              jp.game_referal_amount,
              jp.country,
              jp.country_name,
              jp.state,
              jp.location,
              jp.state_abb,
              jp.game_type,
              ja.id AS appliedId,
              IFNULL(ja. STATUS, '') AS appliedStatus,
              IFNULL(ja.admin_review, '') AS admin_review,
              ja.is_req_referal_check,
              usr.id AS memberId,
              rja.email AS appliedEmail,
              rja.game_id,
              rja.referred_id,
              mem.state_abb AS memStateAbb,
              mem.state AS memState,
              mem.zipcode AS memZipcode,
              mem.city AS memCity,
              mem.country_name AS memCountryNme,
              mem.country_code AS memCountry,
              usc. NAME AS appliedMemberName,
              usc.first_name,
              IFNULL(
                  mem.primary_contact,
                  usc.phone_number
              ) AS primary_contact,
              usc.last_name,
              usc.profileimage_path AS addressbookImage,
              usr.profile_image AS userImage,
              usr.last_login,
              mem.user_experience_year,
              mem.user_experience_month,
              mem.current_designation,
              mem.current_player,
              rja.create_date AS appliedDate,
              rja.addressbook_id,
              IFNULL(ja.joining_date, '') AS joiningdate,
              jp. STATUS AS gameStatus,
              jp.games_referal_amount_type AS gameReferalAmountType,
              jp.game_freeze_status AS gameFreezeStatus,
              jp.game_freeze_message AS gameFreezeMsg,
              ja.app_assign_back_to_rp_count,
              ja.applied_source,
              MAX(rja.id) AS max_id,
              GROUP_CONCAT(
                  jp.title
                  ORDER BY
                      rja.create_date DESC
              ) AS gamesApplied,
              GROUP_CONCAT(DISTINCT(jp.id)) AS gamesAppliedId,
              COUNT(DISTINCT(jp.id)) totalgameApplied,
              ja.application_assign_to_rp_status,
              1 AS rpAppliedSource,
              rja.create_date AS applied_on
          FROM
              (`refer_gameapplied` AS rja)
          JOIN `games_post` AS jp ON `jp`.`id` = `rja`.`game_id`
          JOIN `user_socialconnections` AS usc ON `rja`.`addressbook_id` = `usc`.`id`
          LEFT JOIN `user_user` AS usr ON `usr`.`email` = `rja`.`email`
          LEFT JOIN `user_member` AS mem ON `mem`.`user_id` = `usr`.`id`
          LEFT JOIN `game_applied` AS ja ON `ja`.`id` = `rja`.`applied_id`
          WHERE
              `rja`.`referby_id` = '2389'
          GROUP BY
              `rja`.`email`           
      )
      UNION ALL
          (
              SELECT
                  ja.previous_status,
                  ja.rejected_status,
                  ja.rejection_reason_text,
                  ja.rejection_reason,
                  jr.id AS rjaId,
                  jrtm. STATUS AS refer_applied_status,
                  jp.title,
                  jp.user_user_id AS playerId,
                  jp.id AS gameId,
                  jp.posted_on AS gamePostDate,
                  jp.game_referal_amount,
                  jp.country,
                  jp.country_name,
                  jp.state,
                  jp.location,
                  jp.state_abb,
                  jp.game_type,
                  ja.id AS appliedId,
                  IFNULL(ja. STATUS, '') AS appliedStatus,
                  IFNULL(ja.admin_review, '') AS admin_review,
                  ja.is_req_referal_check,
                  usr.id AS memberId,
                  jrtm.referto_email AS refappliedEmail,
                  jr.game_id,
                  jrtm.id,
                  mem.state_abb AS memStateAbb,
                  mem.state AS memState,
                  mem.zipcode AS memZipcode,
                  mem.city AS memCity,
                  mem.country_name AS memCountryNme,
                  mem.country_code AS memCountry,
                  usc. NAME AS appliedMemberName,
                  usc.first_name,
                  IFNULL(
                      mem.primary_contact,
                      usc.phone_number
                  ) AS primary_contact,
                  usc.last_name,
                  usc.profileimage_path AS addressbookImage,
                  usr.profile_image AS userImage,
                  usr.last_login,
                  mem.user_experience_year,
                  mem.user_experience_month,
                  mem.current_designation,
                  mem.current_player,
                  jrtm.refer_on AS appliedDate,
                  jrtm.referto_addressbookid,
                  IFNULL(ja.joining_date, '') AS joiningdate,
                  jp. STATUS AS gameStatus,
                  jp.games_referal_amount_type AS gameReferalAmountType,
                  jp.game_freeze_status AS gameFreezeStatus,
                  jp.game_freeze_message AS gameFreezeMsg,
                  ja.app_assign_back_to_rp_count,
                  ja.applied_source,
                  MAX(jrtm.id) AS max_id,
                  GROUP_CONCAT(
                      jp.title
                      ORDER BY
                          jr.refer_on DESC
                  ) AS gamesApplied,
                  GROUP_CONCAT(DISTINCT(jp.id)) AS gamesAppliedId,
                  COUNT(DISTINCT(jp.id)) totalgameApplied,
                  ja.application_assign_to_rp_status,
                  2 AS rpAppliedSource,
                  jrtm.refer_on AS applied_on
              FROM
                  (`game_refer` AS jr)
              JOIN `game_refer_to_member` AS jrtm ON `jrtm`.`rid` = `jr`.`id`
              JOIN `games_post` AS jp ON `jp`.`id` = `jr`.`game_id`
              JOIN `user_socialconnections` AS usc ON `jrtm`.`referto_addressbookid` = `usc`.`id`
              LEFT JOIN `user_user` AS usr ON `usr`.`email` = `jrtm`.`referto_email`
              LEFT JOIN `user_member` AS mem ON `mem`.`user_id` = `usr`.`id`
              LEFT JOIN `game_applied` AS ja ON `ja`.`referred_by` = `jrtm`.`id`
              WHERE
                  `jrtm`.`status` = '1'
              AND `jr`.`referby_user_id` = '2389'
              AND `jrtm`.`refer_source` NOT IN ('4')
              GROUP BY
                  `jrtm`.`referto_email`
          )
  ) a
                  GROUP BY
  a.appliedEmail
                  ORDER BY
  a.gamesAppliedId DESC
  </code></pre>