Select all rows except top row in Maria DB

Question

How do I return all rows from a table except the first row. Here is my sql statement:

SELECT * FROM article  where article_no 
NOT IN
(SELECT * FROM article   order by article_no  limit 1)

The up given SQL query throws an error:

This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Try `NOT IN (SELECT article_no FROM article order by article_no limit 1)` — RiggsFolly, Dec 20 '17 at 19:46
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NOT IN (SELECT * FROM article order by article_no limit 1)' @RiggsFolly — Ritesh Aidasani, Dec 20 '17 at 19:56
For heavens sake ___Thats your original code___ Not the amendment I suggested — RiggsFolly, Dec 20 '17 at 19:57
Possible duplicate of [MySQL skip first 10 results](https://stackoverflow.com/questions/2827029/mysql-skip-first-10-results) — Sam M, Dec 20 '17 at 20:24
Use the OFFSET keyword. See https://stackoverflow.com/questions/2827029/mysql-skip-first-10-results — Sam M, Dec 20 '17 at 20:24

Sonny · Accepted Answer · 2017-12-20T20:25:10.790

1

The subquery must select only the column you need for comparison:

SELECT *
FROM article
WHERE article_no NOT IN (
    SELECT article_no
    FROM article
    ORDER BY article_no
    LIMIT 1
)

The above query will work in MySQL, but not in MariaDB, which doesn't currently support LIMIT in subqueries. I would rewrite the subquery like this:

SELECT *
FROM article
WHERE article_no NOT IN (SELECT MIN(article_no) FROM article)

edited Dec 20 '17 at 20:25

answered Dec 20 '17 at 19:55

Sonny

1

Not that I am overly bothered, but that is kinda.. well very very very like my comment – RiggsFolly Dec 20 '17 at 19:58
1

Hmmm my comment has been there for 9 minutes before your answer was posted! I guess you must be a slow typer – RiggsFolly Dec 20 '17 at 20:01
1

@RiggsFolly - I read the question, but not the comments. I wrote an answer because there wasn't one already. I then read your comments and noticed that you'd recommended the same thing. I can delete my answer if you'd rather put yours. – Sonny Dec 20 '17 at 20:04
1

Like i said "Am I bovered" – RiggsFolly Dec 20 '17 at 20:07
@RiggsFolly - Thanks the amendment but that didn't actually solved the use of **limit** function in my sub query – Ritesh Aidasani Dec 20 '17 at 20:18

Rick James · Answer 2 · 2018-01-02T17:00:47.517

1

This skips the row with the lowest value of article_no:

SELECT *
    FROM article
    ORDER BY article_no ASC
    LIMIT 999999999
    OFFSET 1;

Perhaps you want DESC (instead of ASC) to skip the highest value?

Be aware that NOT IN ( SELECT ... ) is a very inefficient construct.

edited Jan 02 '18 at 17:00

answered Dec 26 '17 at 03:50

Rick James

This doesn't work, as `OFFSET` cannot be used without `LIMIT` in MySQL or MariaDB – Sonny Jan 02 '18 at 13:35
1

@Sonny - Oops. I added `LIMIT` with a huge value to make it work. – Rick James Jan 02 '18 at 17:01

2 Answers2