phpmyadmin Question posted in
The official documentation can be found here.

How to display the last record from the group by and display the remaining records? – Phpmyadmin

I check on SO but I haven’t found the solution.

I am using CodeIgniter.

I have a table with the same member_id. What I am doing is I have to display the all the member_id but if member_id is same then display the last record of that member id. I tried below query but still not working for me.

How do I get a group by with the last record added?

This is the table

enter image description here

After using the query I am getting this output
enter image description here

Now notice here member_id 337 and 343 is twice in the table and I am getting the first record instated of last.

enter image description here

Please check this query

SELECT  MAX(member_id),membershipForTheYear,membership_added_date 
FROM membership_details GROUP BY member_id 
ORDER BY membership_added_date DESC

I am using phpmyadmin. I export the file open it and added code here.

CREATE TABLE `membership_details` (
  `membership_id` int(11) NOT NULL,
  `member_id` int(11) NOT NULL,
  `membership_added_date` varchar(100) NOT NULL,
  `membershipForTheYear` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `membership_details` (`membership_id`, `member_id`, `membership_added_date`, `membershipForTheYear`) VALUES
(1, 209, '23-02-2019 02:40:22', '2018-2019'),
(2, 337, '22-02-2019 02:47:10', '2018-2019'),
(3, 2, '23-02-2019 06:36:40', '2019-2020'),
(4, 337, '23-02-2019 21:15:08', '2019-2020'),
(5, 343, '24-02-2019 15:07:05', '2018-2019'),
(6, 343, '24-02-2019 15:09:20', '2019-2020');

I tried query

SELECT * 
FROM membership_details WHERE (membership_id,member_id) IN 
( SELECT membership_id, MAX(membership_id)
  FROM membership_details
  GROUP BY member_id
)

CodeIgniter

/*sub query*/
    $this->db->select('member_id, membershipForTheYear, membership_added_date, max(membership_id) as membership_id')->from('membership_details')->group_by('member_id');
    $subQuery =  $this->db->get_compiled_select();
    /*end sub query*/

    $get_s_member = array('members.member_type' =>2,'members.is_Approved'=>1,'members.is_status'=>1,'relation_member.primary_customer_id' =>$gotPrimaryCustid);
        $this->db->select('*');
        $this->db->from('members'); 
        $this->db->join('('.$subQuery.') as membership_details', 'members.member_id = membership_details.member_id','LEFT');
            $this->db->where($get_s_member);

            $query = $this->db->get();
            $result   = $query->result();
           // print_r($result);

            echo $this->db->last_query();
         if($result)
        {
             return $result;  
        }
        else 
        {
           return 0;  
        }
Tags:

2

Answers


  1. 0
    • Ok, the last row for each group will always have the maximum ID.
    • So, the first subquery below selects member_id with maximum(a.k.a last) membership_id.
    • Now, we do an inner join of the current table with above query derived table based on membership_id and show the details.

    SQL:

    select m1.membership_id,m1.member_id,m1.membership_added_date,m1.membershipForTheYear
from membership_details m1
inner join (select member_id,max(membership_id) as membership_id
from membership_details 
group by member_id) m2
on m1.membership_id = m2.membership_id;
    Login or Signup to reply.
  2. 0

    If the membership_id is AUTO_INCREMENT PRIMARY KEY, and it’s ok to select the row with the highest value in it, then you can use:

    SELECT *
FROM membership_details
NATURAL JOIN (
    SELECT MAX(membership_id) as membership_id
    FROM membership_details
    GROUP BY member_id
) sub

    You can also just fix, what you already have tried, which is:

    SELECT * 
FROM membership_details WHERE (membership_id,member_id) IN 
( SELECT membership_id, MAX(membership_id)
  FROM membership_details
  GROUP BY member_id
)

    change it to:

    SELECT * 
FROM membership_details WHERE (membership_id,member_id) IN 
( SELECT MAX(membership_id), member_id
  FROM membership_details
  GROUP BY member_id
)

    But if membership_id is PRIMARY KEY, you don’t need member_id in the WHERE clause, and the following should also work:

    SELECT * 
FROM membership_details WHERE (membership_id) IN 
( SELECT MAX(membership_id)
  FROM membership_details
  GROUP BY member_id
)

    As you can see, the subquery is the same as in my JOIN query.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search