rdhd
rdhd

Reputation: 95

How to insert rows in reverse order?

What I'm trying to do is copy the rows of one table to another table. I have 2 choices to copy them on direct order or reverse order. I have no problem with direct order. But I couldn't insert them in reverse order. I'm trying to do it with ORDER BY DESC, but DESC isn't working with subqueries. So I'm doing it with statement. And SELECT returns me in reverse order by PRIMARY KEY. But data in DB my_db_copy inserted in direct order. How can I insert them in reverse order?

set @Query1=Concat ('INSERT INTO my_db_copy.test1 SELECT * FROM my_db.test1 
ORDER BY ', (   SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE (TABLE_SCHEMA = 'my_db')
  AND (TABLE_NAME = 'test1')
  AND (COLUMN_KEY = 'PRI')), ' DESC');
  PREPARE stmt FROM @Query1;
  EXECUTE stmt;    

Upvotes: 0

Views: 1352

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

SQL tables represent unordered sets. There is no such thing as "reversing" the order.

You can assign values to an auto-incremented column to specify a particular ordering.

If you want results in a particular order when you issue a query, then you need to explicitly include an order by. There is no other way to guarantee a result set in a particular order.

Upvotes: 3

Related Questions