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

Question

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

After using the query I am getting this output

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

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;  
        }

@zeitnot, How you did my query like this? I tried command + k and it displayed in one line. — user9437856, Feb 24 '19 at 12:55
you would click `edit` and see what happened there. It is not a black box :) — zeitnot, Feb 24 '19 at 13:02
@user9437856 why do you need `max(member_id)` in the result? — nice_dev, Feb 24 '19 at 13:14
@vivek_23, I experimented it to get my output. You can change it. I thought. there are more then one same id so I can group it and using DESC I can get the last record — user9437856, Feb 24 '19 at 13:16
Possible duplicate of [SQL select only rows with max value on a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Paul Spiegel, Feb 24 '19 at 13:45
@PaulSpiegel, I tried that link but still, I am getting the issue. you can try with my query. — user9437856, Feb 24 '19 at 13:48
"I tried that link" - Post the code which you have tried in your question. — Paul Spiegel, Feb 24 '19 at 13:53
@PaulSpiegel, I added the query in the question which I tried from that link. — user9437856, Feb 24 '19 at 14:02

score 2 · Answer 1 · answered Feb 24 '19 at 13:31

2

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;

answered Feb 24 '19 at 13:31

nice_dev

17,053
2
21
35

give me some time to check your query. – user9437856 Feb 24 '19 at 14:07
How do I convert your code with my CodeIgniter code? I updated the code in the question – user9437856 Feb 24 '19 at 18:11
@user9437856 Not a codeIgniter developer. You can have a look at [this documentation](https://www.codeigniter.com/userguide3/database/queries.html) and execute query directly. – nice_dev Feb 24 '19 at 18:54

score 1 · Answer 2 · answered Feb 24 '19 at 14:07

1

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.

answered Feb 24 '19 at 14:07

Paul Spiegel

30,925
5
44
53

The query which I tried is that best for the future? Yes, membership_id is auto increment. – user9437856 Feb 24 '19 at 14:09
@user9437856 - It returns correct results, but might be slower than the JOIN solution. That however depends on the optimizer. I would juse the JOIN solution, because I don't believe that it will ever be slower. – Paul Spiegel Feb 24 '19 at 14:12
I thought, the subquery is faster than JOIN – user9437856 Feb 24 '19 at 14:19
@user9437856 "subquery is faster than JOIN" - Mabe in a very old version. But if in doubt: Create a test table with a lot of dummy data and run your benchmarks. – Paul Spiegel Feb 24 '19 at 14:51
I added codeignator code. can you assiste me how to convert msql query in codeignator? – user9437856 Feb 24 '19 at 18:34

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

2 Answers2