CODEWITHSUNDEEP
mysqlsqlsql-server
Nikhil Khokale
Nikhil Khokale

Reputation: 25

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.

Upvotes: 0

Views: 50

Answers (3)

Siyual
Siyual

Reputation: 16917

I inserted records through this below query

...query using union...

Now I want these records in the same order in which it is inserted.

Surprisingly, you are retrieving the records in the order in which they were inserted. Using UNION between each of the SELECT statements on your INSERT is causing the records to be sorted before being inserted. UNION does an inherent DISTINCT over all of the results. Switching this to UNION ALL will eliminate the inherent ordering.

HOWEVER...

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.

Unfortunately, this is not possible. SQL tables represent unordered sets. It has no native concept over either the order of the records or the order of which they were inserted.

When I run the query select * from Tbl_EmployeeName it will arrange the records in default order by Name asc.

This is false. As mentioned above, there is no default order that is returned. Any result that you may have gotten when executing that query is merely coincidental. Without specifying an ORDER BY clause, the order is not guaranteed.

Could you please suggest me is there any way to get this in proper sequence in which records are entered

Contrary to your question, you can do this by adding a new column to your table. By setting up the table as follows:

Create Table Tbl_EmployeeName 
(
    Id    Int Identity(1,1) Not Null,
    Name  Varchar (10) -- Or whatever your size is
);

Then doing your inserts:

Insert  Tbl_EmployeeName 
        (Name)
Values  ('Z'),
        ('y'),
        ('x'),
        ('w'),
        ('v'),
        ('u'),
        ('t'),
        ('s')

And querying:

Select  Name
From    Tbl_EmployeeName
Order By Id Asc

Upvotes: 1

Bernd Buffen
Bernd Buffen

Reputation: 15057

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;

Upvotes: 0

GavinCattell
GavinCattell

Reputation: 3963

Unfortunately the order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified. source

You'll need to add a new column if possible which will store the entry time, or a sequence which you can order on.

I would recommend a key/sequence as this can then be used to join other tables more efficiently (source).

Upvotes: 0

Related Questions