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!

__________________
The wiseman owns little but knows much, while the fool knows little but owns much

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

__________________
All cookies cleared!

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?

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

Paul Waters

Create a field for a timestamp.

Use it.

__________________


Paul

sarettah

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.

__________________
All cookies cleared!

malakajoe

__________________
Selfpleasure.com for sale on auction. Closes on Tuesday March 11th at 9pm PST!!!!

Dirty enough to be good, but clean enough for everyone!
------------------------------------------------------------------

Moral Police - First graduating class coming soon! - Forcing our values across the internet

cezam

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

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....

__________________
All cookies cleared!