ansaurus

Question

Answer 1

+3 A:

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 2008-11-24 21:52:12

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 2008-11-24 22:17:01

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 2008-11-24 22:40:15

Answer 2

+1 A:

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.

le dorfier 2008-11-24 21:54:33

Probably should be SELECT DISTINCT t1.columns ...

Bill Karwin 2008-11-24 22:18:00

I'm sure you forgot a DISTINCT in there.

Jason Lepack 2008-11-24 22:22:32

MySQL: How to select all rows from a table EXCEPT the last one