how to order datas by insert sequence DESC ?

Question

i has a table:TestTable, this table only has one field: value,i insert same datas:

value
------------
ccc
aa
111
bbb
------------

i run:

select * from TestTable

can show my result order by:

ccc
aa
111
bbb

now,i want get this result DESC:

bbb
111
aa
ccc

how to create this sql?

select * from TestTable order by (by what?) DESC

thak you!

Answer 1

There is no automatic "creation order" identifier that could be used in an order statement. You should use an auto_increment column to number your records. You can then order by that column.

MySQL docs on auto_increment

Answer 2

Would something like this work?

SELECT @rownum := @rownum +1 `rank`,
       T.*
  FROM TestTable T,
       ( SELECT @rownum :=0 ) T
 ORDER BY `rank` DESC 

Answer 3

Sound like you want to sort using a custom sort order. Two approaches spring to mind: 1) put the sort order in a table and use that table in the query; 2) use a delimited string (assuming the number of values in the domain is small, stable and the values of similar and 'narrow' widths).

Please excuse the SQL Server syntax but fairly standard and hopefully you get the general idea:

e.g. 1

WITH MyTable (ID, data_col)
     AS
     (
      SELECT ID, data_col
        FROM (
              VALUES (1, 'aa'), 
                     (2, '111'), 
                     (3, 'ccc'), 
                     (4, 'bbb'), 
                     (5, '111'), 
                     (6, 'aa'), 
                     (7, '111'), 
                     (8, 'bbb')
             ) AS MyTable (ID, data_col)
     ), 
     MyDomainWithSortOrder (domain_col, sort_seq)
     AS
     (
      SELECT ID, domain_col
        FROM (
              VALUES ('bbb', 1), 
                     ('111', 2), 
                     ('aa', 3), 
                     ('ccc', 4)
             ) AS MyDomainWithSortOrder (ID, domain_col)
     )
SELECT T1.ID, T1.data_col, D1.sort_seq
  FROM MyTable AS T1
       INNER JOIN MyDomainWithSortOrder AS D1
          ON T1.data_col = D1.domain_col
 ORDER 
    BY sort_seq;

e.g. 2

WITH MyTable (ID, data_col)
     AS
     (
      SELECT ID, data_col
        FROM (
              VALUES (1, 'aa'), 
                     (2, '111'), 
                     (3, 'ccc'), 
                     (4, 'bbb'), 
                     (5, '111'), 
                     (6, 'aa'), 
                     (7, '111'), 
                     (8, 'bbb')
             ) AS MyTable (ID, data_col)
     )
SELECT ID, data_col
  FROM MyTable
 ORDER 
    BY CHARINDEX(CAST(data_col + ' ' AS CHAR(3)), 'bbb111aa ccc');