CODEWITHSUNDEEP
mysql
Mickey
Mickey

Reputation: 2405

mysql getting getting next and previous rows

This is an example query I got off of stack overflow. I understand how it works, but if id is the primary key isn't the whole order by id desc/asc useless? I mean id<4 isn't going to return a higher key is it? And doesn't the whole order by just slow up the query? 

select * from table where id=4 limit 1
union all
(select * from table where id<4 order by id desc limit 1)
union all
(select * from table where id>4 order by id asc limit 1)

Upvotes: 0

Views: 90

Answers (3)

fthiella
fthiella

Reputation: 49089

SQL Tables have no default order.

You might notice that in MySQL/InnoDB if ID is your primary key the following two queries will return the same result:

SELECT * FROM table WHERE id>4 ORDER BY id ASC LIMIT 1
SELECT * FROM table WHERE id>4 LIMIT 1

because in MySQL/InnoDB records are usually ordered by the primary key: but this is not documented and there are no guarantees that you will always get the next record. The following two queries are different also in MySQL/InnoDB:

SELECT * FROM table WHERE id<4 ORDER BY id DESC LIMIT 1
SELECT * FROM table WHERE id<4 LIMIT 1

if id=3 is present in your table, the first query will return that row, while the second could return id=3 but it will (probably, it is not guaranteed) return the row with the least ID, for example id=1.

As a general rule, you always need to use an ORDER BY clause when you are using a LIMIT in your query, otherwise the resulting rows could be undetermined.

Upvotes: 2

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7242

Well,

In the second select, its order the Id's smaller than 4 by DESCENDING from top to down.

In the third select, its ordering the Id's larger than 4 by ASCENDING order.

I suppose the reason for this is because he want the Id nr 4 first, then the first lower value than 4 then the highest value over 4 .

So the result he wants here is 4,lower than 4,higher than 5. If this is the right way to achieve this can be discussed.

It wont slow down the query notably, its getting 3 records in total and ordering by a PK is very effective.

The ORDER BY is there since < 4 will usually select the first id in ASC order.

Try this

CREATE TABLE `id` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Add 6 Rows.

INSERT INTO `id` (`Id`) VALUES ('2');
INSERT INTO `id` (`Id`) VALUES ('3');
INSERT INTO `id` (`Id`) VALUES ('4');
INSERT INTO `id` (`Id`) VALUES ('5');
INSERT INTO `id` (`Id`) VALUES ('6');

Run this

SELECT * FROM id WHERE Id < 5 LIMIT 1;

Without the ORDER BY, id 1 is selected. So while "But a primary key isn't going to be out of order in the table" is true, without the order by DESC it will order ASC.

Upvotes: -1

Italo Borssatto
Italo Borssatto

Reputation: 15699

Being primary key only, doesn't garantee that removing order by will give the same result. But if id is auto incremented, yes.

Upvotes: 0

Related Questions