Question 368833 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL: How to get the last record which type equal to 3 including to fetch rest type rows?

I have a table and I want to fetch the lastest row which type = 3. based on the user_id also rest of the rows fetched which not type=3

Table – galleries

+----+----------+------------+----------+
| id | user_id  | type       |  photo   |
+----+----------+------------+----------+
|  1 | 1        | 1          | img1.jpg |
|  2 | 2        | 1          | img2.jpg |
|  3 | 1        | 3          | img3.jpg |
|  4 | 1        | 2          | img4.jpg |
|  5 | 1        | 3          | img5.jpg |
|  6 | 3        | 1          | img6.jpg |
|  7 | 2        | 3          | img7.jpg |
+----+----------+------------+----------+

In above table, user_id=1 has more than 1 records which type=3. So in this case, I want to show the last record of this user using MySql Query.

My Output should be like:

+----+----------+------------+----------+
| id | user_id  | type       |  photo   |
+----+----------+------------+----------+
|  1 | 1        | 1          | img1.jpg |
|  2 | 2        | 1          | img2.jpg |
|  4 | 1        | 2          | img4.jpg |
|  5 | 1        | 3          | img5.jpg |
|  6 | 3        | 1          | img6.jpg |
|  7 | 2        | 3          | img7.jpg |
+----+----------+------------+----------+

Please let me know how can I prepare a MySQL query for this?

Tags:

2

Answers


  1. 0

    Assuming that "last record" means the one with the highest Id you can do something like this:

    SELECT MAX(id), user_id, type, photo 
FROM mytable
WHERE type=3
GROUP BY user_id

    This query will filter only records that have type=3, group filtered records by user_id and for each group return only the record with the highest id. If this is the criteria to identify a record as last then the query should return what you are looking for

    Login or Signup to reply.
  2. 0

    You can use ROW_NUMBER() to identify the rows that are duplicate, by PARTITION-ing the row numbering according to your requirements, then exclude them by filtering on the row number. I’ve used a derived table (i.e. select from () x), but you can achieve the same thing with a CTE or temp table.

    First we create a table for the sample data:

    create table userphoto (id int, user_id int, type int, photo varchar(10));
                        
insert into userphoto (id, user_id, type, photo)
values
(1, 1, 1, 'img1.jpg'),
(2, 2, 1, 'img2.jpg'),
(3, 1, 3, 'img3.jpg'),
(4, 1, 2, 'img4.jpg'),
(5, 1, 3, 'img5.jpg'),
(6, 3, 1, 'img6.jpg'),
(7, 2, 3, 'img7.jpg');

    Then we select from it, rank and filter.

    select id, user_id, type, photo
from
(
  select 
      row_number() over(partition by user_id, type order by id desc) as r,
      id, user_id, type, photo
  from userphoto 
) x
where x.r = 1
order by id;

    And we see the results:

    id user_id type photo
    1 1 1 img1.jpg
    2 2 1 img2.jpg
    4 1 2 img4.jpg
    5 1 3 img5.jpg
    6 3 1 img6.jpg
    7 2 3 img7.jpg

    Here’s a dbfiddle demonstrating it.

    BTW you will need to be running MYSQL v8.0 or greater for the ROW_NUMBER() function to be supported. If not, here’s a stack overflow post discussing this change.

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

Back To Top
Search