select all rows except the four most recent

Question

I have a table (news), I try to select all rows except the four most recent. The table have a field news_date (date format) and news_id (autoincremet). The result should be desc.

MySQL version: 5.0

Table structure

news_id       (tinyint)  
news_title    (text)  
news_date     (date)

I tried this

Select *
FROM news AS n
   JOIN
       ( SELECT news_id 
         FROM news 
         ORDER BY news_id       
           LIMIT 1 OFFSET 4
       ) AS lim
     ON n.news_id < lim.news_id ;

Can anyone help me with this query?

can you post at least the structure of your table – Mark Feb 06 '13 at 06:35 — Mark, Feb 06 '13 at 06:35

score 1 · Answer 1 · edited Feb 06 '13 at 07:18

1

The LIMIT cause allows you to set an offset.

SELECT * FROM mytable ORDER BY news_date LIMIT 3,18446744073709551615;

The offset of the initial row is 0 (not 1). For more info read 'SELECT Syntax' in the MySQL manual.

edited Feb 06 '13 at 07:18

Arnold Daniels

16,516
4
53
82

answered Feb 06 '13 at 06:35

fionbio

3,368
2
23
38

Where exactly does the 18446744073709551615 number come from? – Jimmy Aug 08 '13 at 19:46
Search for it in the provided link. – fionbio Aug 08 '13 at 23:05

score 0 · Answer 2 · answered Feb 06 '13 at 13:53

0

I would tried the following:

SELECT *
  FROM mytable
 ORDER BY news_date DESC, news_id DESC
 LIMIT 18446744073709551615 OFFSET 3;

answered Feb 06 '13 at 13:53

vyegorov

21,787
7
59
73

select all rows except the four most recent

2 Answers2

Linked