Another yet MySQL MAX() issue

Question

I've a dynamic SQL query:

SET @a=(SELECT MAX(ID) FROM table);
PREPARE STMT FROM 'SELECT * FROM table ORDER BY id DESC LIMIT 0, ?';
EXECUTE STMT USING @a;

That works correctly as expected. However I wanna subtract last 5 rows from this query.

So I used two ways:

1- SET @a=(SELECT (MAX(ID)-5) FROM table);

2- SET @a := @a-5;

The weird thing here is that, it runs but results don't have any differences from the intact query and all rows are fetched!

Any explanations on this topic is really appreciated.

Update #1

There is no gap in the last 20 rows, also if I change 5 to a bigger number like 200 the results are the same.

Update #2

SELECT COUNT(*), MAX(ID) FROM table outputs 211 for COUNT(*) & 577 for MAX(ID)

So if I change 5 to 400 it works, but it excludes first 34 IDs from the result. Also this way of exclusion is true about @Laurence answer.

Update #3

Consider these IDs as total rows in the table table.

| ID |
+----+
| 55 |
| 54 |
| 53 |
| 52 |
| 51 |

See the results about below changes to the query (order is DESC as I defined):

# @a is MAX(id) = 55

SET @a := @a-5;
    => query: SELECT * FROM table ORDER BY id DESC LIMIT 0, 55-5/*50*/ -- no effects in result

SET @a := @a-50;
    => query: SELECT * FROM table ORDER BY id DESC LIMIT 0, 55-50/*5*/ -- no effects in result

SET @a := @a-51;
    => query: SELECT * FROM table ORDER BY id DESC LIMIT 0, 55-51/*4*/ -- will exclude row `51` from the result so `55, 54, 53, 52` are fetched

@Laurence tnx I'll check, and could you explain the problem with MAX() here? — revo, Oct 27 '13 at 15:16
Assuming they are auto_increment, you _cannot_ assume that the IDs will be consecutive with no gaps (from deletions). — Michael Berkowski, Oct 27 '13 at 15:16
@MichaelBerkowski last 20 rows don't have any gaps. however if I change `5` to `200` the results are the same. — revo, Oct 27 '13 at 15:17
You should try changing the `OFFSET` not the `LIMIT` itself (apart from getting rid of the `max` function). This [question](http://stackoverflow.com/questions/10119291/confusion-in-mysql-limit-when-used-with-offset/10119350#10119350) might help — Mosty Mostacho, Oct 27 '13 at 15:20
@MostyMostacho also I don't think that we can set `OFFSET` from the end? — revo, Oct 27 '13 at 15:22
@revo As you can see in the accepted answer you did change the `OFFSET` and also the `max` ^.^ — Mosty Mostacho, Oct 27 '13 at 20:18
@MostyMostacho that `OFFSET` with this offset has many offsets :D — revo, Oct 27 '13 at 20:48

Laurence · Accepted Answer · 2013-10-27T15:50:28.210

0

It seems you want to exclude the highest 5 ids and sort the results by id descending. To do this you can just start the limit at 5:

Set @a = (Select Count(*) From table);
Prepare stmt From 'Select * From table Order By id desc Limit 5, ?';
Execute stmt Using @a;

To see how this works, imagine the results (id only) before the limit is applied:

55, 54, 53, 52, 51, 50, 49, ...

Limit 5, n says to return n rows starting from the 6th, i.e

50, 49, ...

Here @a just needs to be big enough to get all the results. You could just use a very big number and not worry about being precise. Assuming id is unique, max(id) or count(*) will do in this case.

edited Oct 27 '13 at 15:50

answered Oct 27 '13 at 15:31

Laurence

10,896
1
25
34

order is desc but it excludes first 5 rows! – revo Oct 27 '13 at 15:33
for now I'm not worry about the big number, but the way the result is shown. it cuts the rows from the beginning not from the latest rows! – revo Oct 27 '13 at 15:53
@revo yes. Nowhere does it say, if I have this data in my table, then I would like this answer. – Laurence Oct 27 '13 at 15:58
it's a example data though, that does have no difference from main data. it's just in smaller scale. – revo Oct 27 '13 at 16:00
@revo Did you try the last update? This cuts from the other end. – Laurence Oct 27 '13 at 16:11
oh yes. but why?? I don't understand!! – revo Oct 27 '13 at 16:20
MySQL first orders the results, then applies the limit. – Laurence Oct 27 '13 at 16:26

Another yet MySQL MAX() issue

1 Answers1