9

How can I select all BUT the last row in a table?

I know I can select the last via SELECT item FROM table ORDER BY id DESC LIMIT 1 but I want to select ALL but the last.

How can I do this?

Selecting a certain amount and using ASC won't work because I won't know how many rows there are.

I thought about doing a row count and taking that amount and selecting that -1 and using ORDER BY id ASC but are there any easier ways?

NoDataDumpNoContribution
  • 10,591
  • 9
  • 64
  • 104
user2888263
  • 243
  • 1
  • 3
  • 7

5 Answers5

12

If Id is unique, you can do it as follows:

SELECT ...
FROM MyTable
WHERE Id < (SELECT MAX(Id) FROM MyTable)
Joe
  • 122,218
  • 32
  • 205
  • 338
10

The easiest way to do this is to discard the row in the application. You can do it in SQL by using a calculated limit. Calculate it as (SELECT COUNT(*) FROM T) - 1.

Or:

SELECT *
FROM T
EXCEPT
SELECT TOP 1 *
FROM T
ORDER BY id ASC

Excluding the last row.

usr
  • 168,620
  • 35
  • 240
  • 369
1

Something like this should work

 select * 
 from table
 where id not in (SELECT id FROM table ORDER BY id DESC LIMIT 1)
Hogan
  • 69,564
  • 10
  • 76
  • 117
  • Performance wise, this is horrible. If you have `n` rows in your table, this results in `O(n^2)` operations as it traverses the generated list for every row – Ruben Helsloot Oct 26 '18 at 14:57
  • @RubenHelsloot -- sounds like someone didn't create an index. – Hogan Oct 30 '18 at 16:45
  • @RubenHelsloot The subquery result set is an invariant and is only retrieved once per the whole query. The performance is `O(n)` even without indexes. Just run `EXPLAIN` and see for yourself. – Ilya Semenov Nov 12 '19 at 18:13
1

If you are fine with having results sorted by DESC, then you could also use any of the answers from: How to skip the first n rows in sql query to achieve this.

This approach can also be easily generalized to "select all but the last N rows".

Unfortunately, there doesn't seem to be a single standardized syntax for it, each DBMS has its own.

E.g. in my answer I cover PostgreSQL, SQLite and MysQL. In PostgreSQL for example you could just use OFFSET without LIMIT:

SELECT item FROM table ORDER BY id DESC OFFSET 1

There doesn't seem to be a super nice way to also keep the order though without subqueries: SQL LIMIT, but from the end

Ciro Santilli OurBigBook.com
  • 347,512
  • 102
  • 1,199
  • 985
0

Since LIMIT can't get parameters you have two options - Build a dynamic query (see this post for more details - passing LIMIT as parameters to MySQL sproc) or filter it in your application.

Community
  • 1
  • 1
Yosi Dahari
  • 6,794
  • 5
  • 24
  • 44