CODEWITHSUNDEEP
sqlmysql
Koerr
Koerr

Reputation: 15723

how to order datas by insert sequence DESC?

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!

Upvotes: 0

Views: 1112

Answers (3)

onedaywhen
onedaywhen

Reputation: 57023

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');

Upvotes: 1

Mark Baker
Mark Baker

Reputation: 212412

Would something like this work?

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

Upvotes: 1

Pekka
Pekka

Reputation: 449395

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

Upvotes: 4

Related Questions