Why same query giving two different result?

Question

I have created two tables & inserted values as shown below .

Table 1

create table maxID (myID varchar(4));

insert into maxID values ('A001');
insert into maxID values ('A002');
insert into maxID values ('A004');
insert into maxID values ('A003');

Table 2

create table maxID2 (myID varchar(4) PRIMARY KEY);

insert into maxID2 values ('A001');
insert into maxID2 values ('A002');
insert into maxID2 values ('A004');
insert into maxID2 values ('A003');

When I execute query

SELECT myId, @rowid:=@rowid+1 as myrow 
FROM maxID, (SELECT @rowid:=0) as init
ORDER BY myrow desc
LIMIT 1;

I get output as

+++++++++++++
myid + myrow
+++++++++++++
A003 + 4
+++++++++++++

AND

When I execute query

SELECT myId, @rowid:=@rowid+1 as myrow 
FROM maxID2, (SELECT @rowid:=0) as init
ORDER BY myrow desc
LIMIT 1;

I get output as

+++++++++++++
myid + myrow
+++++++++++++
A004 + 4
+++++++++++++

The difference between two table is that in second table I have myID as PRIMARY KEY.

You can view above data/ result at www.sqlfiddle.com.

My Question is

Why I am getting two different results when query is same?

NOTE : This question is bit related to my old question Getting last record from mysql, where I almost got the answer and Yak informed me that the order of rows are not guaranteed. :(

Well, exactly because of what Yak says: the order is not guaranteed. The difference in order of the underlying table here is most likely because InnoDB tables use a clustered index based on the primary key. The simple answer is that row-order in a table should _never_ matter. — Wrikken, Jun 20 '12 at 18:05

Sebas · Accepted Answer · 2012-06-20T18:20:20.910

This is because when the selected fieldset is totally included into a given index fieldset, this index is used to retrieve the data instead of the fullscan result.

Since indexes have a default sorting order, when raw tables datas don't, the extracted data using the index is therefore appearing in a different order than if it were from a full table scan.

In your case the 4th row is indeed the 4th one when you are using the primary key because internally mysql (oracle, sql server...) organized it this way to seek the data faster.

Please note that by chance, you might have obtained the same result in both queries, just because the default selected result order is indeed not proven to be related to the inserted order.

At last, let me warn you that if you planned to add an index with a specific order in mysql (such as described here) so the rows are retrieved in, let say, the DESC order, you could not do it since it is not an allowed feature yet in mysql:

An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

perfect.... Can you also provide your inputs on [this](http://stackoverflow.com/questions/8923366/getting-last-record-from-mysql) question? — Fahim Parkar, Jun 20 '12 at 18:10
hmm but I'm not sure it would represent something valuable for your question, would it? The problem of your question is that there's no positive answer: no, it is not possible to find out the last inserted row from mysql core only... — Sebas, Jun 20 '12 at 18:36
yeah I saw that, but I'm afraid you can't do that unless you check the rollback logs of mysql if they are available: mysqlbinlog mysql-bin.000001 — Sebas, Jun 20 '12 at 18:41

score 4 · Answer 2 · edited Jul 11 '12 at 15:26

4

Primary keys are returned in sorted order by default

Thats why you are getting different output for both queries.

You can check with another example as well: here is the example

edited Jul 11 '12 at 15:26

Sebas

21,192
9
55
109

answered Jun 20 '12 at 18:18

Nishu Tayal

20,106
8
49
101

Why same query giving two different result?

Table 1

Table 2

AND

My Question is

2 Answers2