1

how to select all records from an special table order by desc ?

pls see my table ,this table defaultly show by its default order , but now I need to get array which order is decs to the default order , how can I do it ?

enter image description here

I want to get: enter image description here

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `s`
-- ----------------------------
DROP TABLE IF EXISTS `s`;
CREATE TABLE `s` (
  `name` varchar(255) NOT NULL default '',
  `age` int(11) default NULL,
  PRIMARY KEY  (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of s
-- ----------------------------
INSERT INTO `s` VALUES ('jimmy', '31');
INSERT INTO `s` VALUES ('darcy', '25');
INSERT INTO `s` VALUES ('kelvin', '30');
INSERT INTO `s` VALUES ('frank', '28');
Praveen Prasannan
  • 7,093
  • 10
  • 50
  • 70
mingfish_004
  • 1,385
  • 2
  • 18
  • 26
  • didn't you find anything about `ORDER BY`? – Netorica Jul 17 '13 at 02:24
  • order by can only sort by character – mingfish_004 Jul 17 '13 at 02:26
  • 1
    What defines your order -- in other words, why frank then kelvin then darcy then jimmy? That's not alphabetic? – sgeddes Jul 17 '13 at 02:45
  • there are no id keys in my table , then if no this normal order keys , can not invert my records? – mingfish_004 Jul 17 '13 at 02:53
  • 1
    Note that the order in which items are returned when you don't specify an `order by` is not defined - generally, it will be the order in which items were inserted, but if there have been deletions and insertions, this may not hold. Note also that the optimiser may choose to [use one or more indexes when retrieving values](http://stackoverflow.com/a/10064571/622391), in which case the items will be sorted based on some combination of those indexes, original insertion order plus fragmentation. It's not to be relied on! – Simon MᶜKenzie Jul 17 '13 at 03:22
  • Having records without primary key is usually a very unprofessional thing, except for n-to-n tables of course. You should consider adding one as suggested by Mahan. But if you cant or don't want to for any reason, Praveen answer is the only way to go. – Havenard Jul 17 '13 at 04:23

2 Answers2

2
SELECT  s.*,
        @curRow := @curRow + 1 AS row_number
FROM    s
JOIN    (SELECT @curRow := 0) r order by row_number desc;

fiddle

Praveen Prasannan
  • 7,093
  • 10
  • 50
  • 70
1

if you want to keep the original sequence of the rows and so you can invert it without alphabetical or numerically ordering the other columns put an auto-incrementing column which will keep the ordering of each row and you can just ORDER BY ASC|DESC

because...

In the SQL world, order is not an inherent property of a set of data.

https://dba.stackexchange.com/questions/6051/what-is-the-default-order-of-records-for-a-select-statement-in-mysql

https://dba.stackexchange.com/questions/5774/why-is-ssms-inserting-new-rows-at-the-top-of-a-table-not-the-bottom/5775#5775

Community
  • 1
  • 1
Netorica
  • 18,523
  • 17
  • 73
  • 108