12

I have a table with N rows, and I wanna select N-1 rows.

Suggestions on how to do this in one query, if it's possible..?

tvanfosson
  • 524,688
  • 99
  • 697
  • 795
Lasse A Karlsen
  • 801
  • 5
  • 14
  • 23
  • Generic version of this question: [SQL Selecting all BUT the last row in a table?](https://stackoverflow.com/q/19470230/11107541) – starball Jun 08 '23 at 00:44

5 Answers5

25

Does the last row have the highest ID? If so, I think this would work:

SELECT * FROM TABLE WHERE ID != (SELECT MAX(ID) FROM TABLE)

MySQL does allow subselects in the current version, right?

However, in most cases, it'd probably perform better if you selected all the rows and then filtered the unwanted data out in your application.

Joshua Carmody
  • 13,410
  • 16
  • 64
  • 83
  • MySQL 4.1 has supported subqueries for more than four years! Though admittedly some hosting companies still use MySQL 4.0 which lacks this feature. – Bill Karwin Nov 24 '08 at 22:17
  • Heh, I thought so. But the last time I used MySQL it was 3.X, and stuff like this didn't work. Mostly using MS SQL Server nowadays. – Joshua Carmody Nov 24 '08 at 22:40
4

Another technique I don't see listed here is

SELECT * FROM table ORDER BY id DESC LIMIT 10000 OFFSET 1;

This will give you the records ordered by id descendant except first, that is except the last in the original order.
Note that with this method you will only take 10000 records, however this number can be as high as you want but cannot be omitted.
The advantage of this method is that you can order by whatever you want.
The disadvantage is that it gives you the records ordered from last to first.
Finally it worths noting that the other methods here works quite well

Redithion
  • 986
  • 1
  • 19
  • 32
2

SELECT DISTINCT t1.columns FROM table t1
INNER JOIN table t2 ON t1.id < t2.id

In my experience, MySQL loves this technique, going back several versions.

dkretz
  • 37,399
  • 13
  • 80
  • 138
0

you must DECLARE a variable and after that SET it. With the help of LIMIT you'll find the n-1 rows

like this:

DECLARE rowsNr INT DEFAULT 0;  
SET rowsNr = SELECT (count(*) AS 'TOTAL_ROWS' FROM TABLE;
SET rowsNr = rowsNr -1;
SELECT * FROM Table WHERE ... LIMIT rowsNr;

if it doesn't work on a normal query try to place it in a StoredProcedure

marisxanis
  • 109
  • 8
-3

Another way to do this could be:

SELECT * FROM table WHERE ID <> LAST_INSERT_ID()

Reference: http://dev.mysql.com/doc/refman/5.7/en/getting-unique-id.html

Devansh Modi
  • 110
  • 5