MySQL sorting issue question

[Mortimer]

Hi,

does anybody know how to sort the rows in order of the last inserted row to the first inserted row without using a specific column for that? When you make a select in MySQL and do not specify any SORT BY clause, it sorts the results in the order they were inserted in the database. Is it possible to get the exact opposite of that?

Thanks for your help!

[M_M]

Create a temporary table, the same structure, except add an autoincrement field

Select from original insert to the temporary table, than select from temporary table order by the autoincrement field descending order.

[fuzebox]

Every table should have a unique id field.

[sarettah]

Without using a unique id somehere in the query, it would be hard to get it using the order clause, however,

Why not just read through your results set backwards:

........

$sql_str="select * from table_i_want_to_read";
$result = mysql_query($sql_str,$db);

//Reading through frontwards

for ($i = 0; $i < mysql_num_rows($result); $i++ )

{

echo "field: ". mysql_result($result,$i,"field" ) ;

}

//reverse the read

for ($i = mysql_num_rows($result); $i>=0; $i-- )

{

echo "field:: ". mysql_result($result,$i,"field" ) ;

}

..........

edited in. The brackets seem to be being replaced with haha123 or some such

[bawdy]

non of these methods are fully accurate... the order that records are returned to a record set are not necessarily in the order that there were inserted... nor are you guaranteed to get the record set back in the same order on any subsequent query

[JSA Matt]

Why on earth would you waste all this time trying to find a way around something when you can simply add a field with a unique ID and use the SORT?

Originally posted by bawdy
non of these methods are fully accurate... the order that records are returned to a record set are not necessarily in the order that there were inserted... nor are you guaranteed to get the record set back in the same order on any subsequent query

They're not? So what is a fully accurate method?

[Paul Waters]

Create a field for a timestamp.

Use it.

[sarettah]

Originally posted by bawdy
non of these methods are fully accurate... the order that records are returned to a record set are not necessarily in the order that there were inserted... nor are you guaranteed to get the record set back in the same order on any subsequent query

Actually, with MYSQL, because it is ISAM, you should by default get back the native order, which is the order the records were added to the table.

With an ISAM table it is a sure thing because the table is built with a default key pointing at the actual record number, with other database structures it is not.

[malakajoe]

Originally posted by Paul Waters
Create a field for a timestamp.

Use it.

[cezam]

Originally posted by JSA Matt
Why on earth would you waste all this time trying to find a way around something when you can simply add a field with a unique ID and use the SORT?

Exactly. Just add an auto_increment primary key, and use it to sort the table.

[Lane]

Rows are not always appended to the end of the table.
If there has been any deleted rows, new rows might be inserted into their place, instead of the end of the table.


Best practice is, either use a primary key with autoincrement or a timestamp field which will keep the insert/update time for each row.

[JSA Matt]

Originally posted by Lane
Rows are not always appended to the end of the table.
If there has been any deleted rows, new rows might be inserted into their place, instead of the end of the table.


Best practice is, either use a primary key with autoincrement or a timestamp field which will keep the insert/update time for each row.

I thought they only did that when the primary key was already present?

[Lane]

here is a little example of why order of the table is not the order of the inserted rows:


CREATE TABLE `a` (
`b` int(11) NOT NULL default '0'
) TYPE=INNODB;

INSERT INTO `a` ( `b` )
VALUES (
'0'
);


INSERT INTO `a` ( `b` )
VALUES (
'1'
);


INSERT INTO `a` ( `b` )
VALUES (
'2'
);

DELETE FROM `a` WHERE `b` = 1;


INSERT INTO `a` ( `b` )
VALUES (
'3'
);


SELECT *
FROM `a`;

this will give you:
0
3
2

not:
0
2
3


See what i mean?

[sarettah]

I see I see said the blind man....

hmmm....