How to sort table alphabetically by name initial?

Question

I have a table contains columns 'employeename' and 'id', how can I sort the 'employeename' column following alphabetical order of the names initial?

Say the table is like this now:

employeename    rid    eid
Dave             1      1
Ben              4      2
Chloe            6      6

I tried the command ORDER BY, it shows what I want but when I query the data again by SELECT, the showed table data is the same as original, indicting ORDER BY does not modify the data, is this correct?

SELECT * 
FROM employee
ORDER BY employeename ASC;

I expect the table data to be modified (sorted by names alphabetical order) like this:

employeename    rid    eid
Ben              4      2
Chloe            6      6
Dave             1      1

in an RDBMS, records in the table are stored in no particular order. Only an explicit order by guarantees that rows will be returned in a desired order. — Kaushik Nayak
– Kaushik Nayak, Commented Feb 1, 2019 at 9:44

user330315 · Accepted Answer · 2019-02-01 10:11:16Z

1

the showed table data is the same as original, indicting ORDER BY does not modify the data, is this correct?

Yes, this is correct. A SELECT statement does not change the data in a table. Only UPDATE, DELETE, INSERT or TRUNCATE statements will change the data.

However, your question shows a misconception on how a relational database works.

Rows in a table (of a relational database) are not sorted in any way. You can picture them as balls in a basket.

If you want to display data in a specific sort order, the only (really: the only) way to do that is to use an ORDER BY in your SELECT statement. There is no alternative to that.

Postgres allows to define a VIEW that includes an ORDER BY which might be an acceptable workaround for you:

CREATE VIEW sorted_employee;
AS
SELECT * 
FROM employee
ORDER BY employeename ASC;

Then you can simply use

select *
from sorted_employees;

But be aware of the drawbacks. If you run select * from sorted_employees order by id then the data will be sorted twice. Postgres is not smart enough to remove the (useless) order by from the view's definition.

Some related questions:

answered Feb 1, 2019 at 10:11

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

one-hand-octopus Over a year ago

Rows in a table (of a relational database) are not sorted in any way. You can picture them as balls in a basket. Does this mean there is no need to sort the data in a database, the data can exist in any order, what matters is how the data is fetched and viewed?

user330315 Over a year ago

@thinkvantagedu: correct (actually you can't really "sort the data in the database")

Collectives™ on Stack Overflow

How to sort table alphabetically by name initial?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related