Nikhil Khokale Nikhil Khokale - 6 months ago 11
MySQL Question

Sorting of string as per its insertion

Scenario: I have table with single column (string). I want to retrieve data which are stored in particular order.

table Tbl_EmployeeName having only one column 'Name'

I inserted records through this below query

Insert Into Tbl_EmployeeName
select 'Z'
union
select 'y'
union
select 'x'
union
select 'w'
union
select 'v'
union
select 'u'
union
select 't'
union
select 's'


Now I want these records in the same order in which it is inserted.
when i run the query:

select * from Tbl_EmployeeName


it will arrange the records in default order by Name asc.
i.e.

result output

s
t
u
v
w
x
y
Z


I want all records in this way

expected output

z
y
x
w
v
u
t
s


Could you please suggest me is there any way to get this in proper sequence in which records are entered, and also without adding any new column to a table.

Answer

You can sort it in your own order with FIND_IN_SET

SELECT * 
FROM Tbl_EmployeeName 
ORDER BY FIND_IN_SET(Name,'Z,y,x,w,v,u,t,s');

If you dont know the insert order use this, then there is direct a AUTOINCREMENT field.

CREATE TABLE `Tbl_EmployeeName` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


    SELECT * 
    FROM Tbl_EmployeeName 
ORDER by id;