Home / Questions / 328988
Accepted
EMCode
Asked: 2020-02-13 14:17:55 +0800 CST 2020-02-13 14:17:55 +0800 CST

SQL query that returns last records according to ID

  • 772

This question is based on this answer.

Thanks to user @Roberto , I was able to modify the following code that makes a nested query to four tables in a database:

SELECT ft.pages_id, 
       ft.data AS titulo, 
       fb.data AS body, 
       fi.data AS imagen, 
       pg.name AS link 
FROM   field_title ft 
       JOIN field_body fb 
         ON ft.pages_id = fb.pages_id 
       JOIN field_images fi 
         ON fi.pages_id = ft.pages_id 
       JOIN pages pg 
         ON pg.id = pg.id 
WHERE  ( ft.pages_id = 1062 ) 
       AND ( pg.id = 1062 ) 
       AND ( fi.created = (SELECT Min(fi2.created) 
                           FROM   field_images fi2 
                           WHERE  fi2.pages_id = ft.pages_id) );

What this does is bring the records corresponding to an ID (1062 in this case). It works well.

What I am doing now is passing those records to a card element of Bootstrap, I realized that with only 1 template of these cards , if the SQL query brings up two rows ( ) or more, the templates of said cardrow are automatically created , that is it's great.

Thinking about it, what I want is that the SQL query brings me the last three records (for example) of a specific table, but depending on the ID field.

The table has this structure:

pages_id      data
------------------------------
1060          Juan Carlos
1062          Ana Maria
1063          Jose Eduardo
1064          Juan Camilo
1068          Mario Andres

The SQL query should return the ID's 1063 , 1064and 1068.

To achieve this, I tried with ORDER BYand LIMIT, like this:

SELECT ft.pages_id, 
       ft.data AS titulo, 
       fb.data AS body, 
       fi.data AS imagen, 
       pg.name AS link 
FROM   field_title ft 
       JOIN field_body fb 
         ON ft.pages_id = fb.pages_id 
       JOIN field_images fi 
         ON fi.pages_id = ft.pages_id 
       JOIN pages pg 
         ON pg.id = pg.id 
WHERE  ( fi.created = (SELECT Min(fi2.created) 
                       FROM   field_images fi2 
                       WHERE  fi2.pages_id = ft.pages_id) ) 
ORDER  BY ft.pages_id DESC 
LIMIT  3;

The problem is that this query brings me the last record (1068) repeated three times.

Is there something wrong with the query?

In this Fiddle you can see the SQL command window and do tests .

Thank you

mysql

3 Answers

  1. Best Answer

    I understand that basically you should search for the last 3 different ones pages_idaccording to this date created, so it would be enough to add this filter to your query:

       and ft.pages_id IN (SELECT *
                           FROM (SELECT DISTINCT pages_id
                                        FROM   field_images
                                        ORDER BY created DESC
                                        LIMIT 3
                                 ) M
                       )

    Why one selectover another select? are the curiosities Mysqlthat it does not allow to use the LIMIT in a subquery but in a subquery of a subquery.

    here the fiddle

    • 2

  2. Your query is fine but the problem is that the last two columns 

    fi.data AS imagen, 
pg.name AS link

    They return different data, therefore, pages_idif you remove those columns and add a distinct , the query is returned without repeating.

    SELECT DISTINCT ft.pages_id, 
       ft.data AS titulo, 
       fb.data AS body 
FROM   field_title ft 
       JOIN field_body fb 
         ON ft.pages_id = fb.pages_id 
       JOIN field_images fi 
         ON fi.pages_id = ft.pages_id 
       JOIN pages pg 
         ON pg.id = pg.id 
WHERE  ( fi.created = (SELECT Min(fi2.created) 
                       FROM   field_images fi2 
                       WHERE  fi2.pages_id = ft.pages_id) ) 

ORDER  BY ft.pages_id DESC
LIMIT  3;

    Now if you need those columns, thepages_id

    • 2

  3. And if you try using the function TOP(), that way you only bring the amount of records that you want, without them being repeated

    SELECT TOP(3)
       ft.pages_id, 
       ft.data AS titulo, 
       fb.data AS body, 
       fi.data AS imagen, 
       pg.name AS link 
FROM   field_title ft 
       JOIN field_body fb 
         ON ft.pages_id = fb.pages_id 
       JOIN field_images fi 
         ON fi.pages_id = ft.pages_id 
       JOIN pages pg 
         ON pg.id = pg.id 
WHERE  ( fi.created = (SELECT Min(fi2.created) 
                       FROM   field_images fi2 
                       WHERE  fi2.pages_id = ft.pages_id) ) 
ORDER  BY ft.pages_id DESC

    Try and tell me how it goes!

    • -1