CODEWITHSUNDEEP
phpmysql
Nuno Miguel Costa
Nuno Miguel Costa

Reputation: 43

In MySQL, how do I select the latest articles EXCEPT for the last 3 articles

In MySQL, when doing a query, how do I select the latest articles except for the last 3 articles? So, let's say I have 100 articles, and I want to select article number 97, 96, and 95; but not article number 98, 99 and 100.

But I need it to be dynamic. So if I insert 100 more articles in my database, then I would need articles number 197, 196 and 195.

What I have right now is this:

$query = "SELECT id, map, title, summary, video, date, publish, difficulty FROM articles WHERE publish = 1 AND map = 'MOVEMENT' AND id > 1 ORDER BY date DESC LIMIT 1";

I think it's the id > 1 that needs to be changed by some math count or something.

Upvotes: 0

Views: 114

Answers (1)

Cid
Cid

Reputation: 15247

You can order your result by id, descending, get only 3 results and use 3 as offset.

In example :

SELECT id,
       map,
       title,
       summary,
       video,
       date,
       publish,
       difficulty
FROM articles
WHERE publish = 1
AND map = 'MOVEMENT'
AND id > 1
ORDER BY id DESC LIMIT 3, 3;

Schema (MySQL v5.7)

CREATE TABLE test
(
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
);

INSERT INTO test VALUES (DEFAULT),
                        (DEFAULT),
                        (DEFAULT),
                        (DEFAULT),
                        (DEFAULT),
                        (DEFAULT),
                        (DEFAULT),
                        (DEFAULT),
                        (DEFAULT),
                        (DEFAULT),
                        (DEFAULT),
                        (DEFAULT),
                        (DEFAULT),
                        (DEFAULT),
                        (DEFAULT),
                        (DEFAULT),
                        (DEFAULT),
                        (DEFAULT),
                        (DEFAULT);

Query #1 : Last id

SELECT MAX(id) AS "Last id"
FROM test;

Output

| Last id |
| ------- |
| 19      |

Query #2 Get the 3 lasts offset 3

SELECT id
FROM test
ORDER BY id DESC LIMIT 3, 3;

Output

| id  |
| --- |
| 16  |
| 15  |
| 14  |

Query #3 Get the 3 lasts offset 3 and fix the order

SELECT * FROM
(
  SELECT id
  FROM test
  ORDER BY id DESC LIMIT 3, 3
) AS t
ORDER BY t.id ASC;

Output

| id  |
| --- |
| 14  |
| 15  |
| 16  |

View on DB Fiddle

Upvotes: 2

Related Questions