# Sort By Natural Ordering

## Question

You are given a column of strings, sort them in natural order.

Natural ordering is simply the ordering that a human would expect.

### Create Table

CREATE TABLE documents (
-- string variable
name varchar(255)
)

### Insert Values Into Table

-- Insert into the table documents
INSERT INTO documents (name)
-- a list of files (notice the numbering)
VALUES
('file1.txt'),
('file2.txt'),
('file3.txt'),
('file4.txt'),
('file5.txt'),
('file6.txt'),
('file7.txt'),
('file8.txt'),
('file9.txt'),
('file10.txt'),
('file11.txt'),
('file12.txt'),
('file13.txt'),
('file14.txt'),
('file15.txt')

### View Table

-- View all the rows
SELECT * FROM documents
name
file1.txt
file2.txt
file3.txt
file4.txt
file5.txt
file6.txt
file7.txt
file8.txt
file9.txt
file10.txt
file11.txt
file12.txt
file13.txt
file14.txt
file15.txt

### View Table Sorted In Non-Natural Order

Notice that simply using ORDER BY creates a non-natural ordering.

-- View all the rows
SELECT * FROM documents
-- Order them alphanumerically
ORDER BY name
name
file10.txt
file11.txt
file12.txt
file13.txt
file14.txt
file15.txt
file1.txt
file2.txt
file3.txt
file4.txt
file5.txt
file6.txt
file7.txt
file8.txt
file9.txt

### View Table Sorted In Natural Order

The solution is to sort by length and then alphanumerically.

-- View all the rows
SELECT * FROM documents
-- Order them by length and then alphanumerically
ORDER BY LENGTH(name), name
name
file1.txt
file2.txt
file3.txt
file4.txt
file5.txt
file6.txt
file7.txt
file8.txt
file9.txt
file10.txt
file11.txt
file12.txt
file13.txt
file14.txt
file15.txt