Mysql select rows except first row

Question

I have issue there with select from table. I want to select all rows except for first row. So .. There is my code

SELECT * FROM table ORDER BY id DESC

So this code select and order id's from table which give me id feedback "5>4>3>2>1". And there is issue .. How I can select and echo just 4>3>2>1 rows. So if I had rows with id's 1,2,6,8,10 , echo will be 10,8,6,2,1 and I want select to echo just 8,6,2,1.

There is my full wrong code for select.

$other = mysql_query("SELECT * FROM table ORDER BY id DESC LIMIT 1, 1");

score 4 · Accepted Answer · answered Dec 05 '17 at 14:21

4

This should do it.

SELECT * FROM table WHERE id NOT IN (SELECT MAX(id) FROM table) ORDER BY id DESC

answered Dec 05 '17 at 14:21

isaace

3,180
1
8
18

Pooshonk · Answer 2 · 2017-12-05T14:20:29.567

1

Try this

$other = mysql_query("SELECT * FROM table ORDER BY id DESC OFFSET 1");

THE ABOVE QUERY WONT WORK AS A LIMIT IS NEEDED

Refer to this answer

edited Dec 05 '17 at 14:20

answered Dec 05 '17 at 14:15

Pooshonk

1,216
2
20
47

Invalid MYSQL syntax, you can't use "offset" without "limit" – evilReiko Dec 05 '17 at 14:16

score 1 · Answer 3 · answered Dec 05 '17 at 14:20

1

Try this:

SELECT *
FROM
(
    SELECT *, row_number()
    OVER (ORDER BY id DESC) row
    FROM table
)
WHERE row != 1

It gives numbers to your selected rows and takes all of them without the one with row number 1

answered Dec 05 '17 at 14:20

SeReGa

686
6
23

score 0 · Answer 4 · answered Dec 05 '17 at 14:20

All you need is offset 1, but offset cannot be used without limit. So, I'd suggest something like:

SELECT * FROM table ORDER BY id DESC LIMIT 99999999 OFFSET 1

Warning: make sure your table doesn't contain lots of records, otherwise you will run into performance issues. Or, change the limit to something reasonable, like 10.

EDIT:

Read: How to use offset without limit

Ali Majed · Answer 5 · 2017-12-05T14:51:28.510

-1

SELECT * 
  FROM table 
  WHERE id NOT IN ( SELECT id 
                      FROM table 
                      ORDER BY id DESC 
                      LIMIT 1 )
ORDER BY id DESC;

You can try this.
In this case I am selecting all the rows, except the one with the biggest id.
In the example of [1,2,3,4,5], it will be :

SELECT * 
  FROM table 
  WHERE id NOT IN ( 5 )
ORDER BY id DESC;

Hope this helps!

edited Dec 05 '17 at 14:51

answered Dec 05 '17 at 14:20

Ali Majed

119
7

Thank you for answer but it sended me feedback " You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DESC LIMIT 1 ) ORDER BY id DESC' at line 1 " .. – Nol Dec 05 '17 at 14:49
Sorry for that. I fixed it. – Ali Majed Dec 05 '17 at 14:51
Specifying the record id is not good as MySQL ids will be changing always. and here we need to skip the last record regardless of the id. – Ruberandinda Patience Mar 24 '19 at 17:37

Mysql select rows except first row

5 Answers5