-1

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
one-hand-octopus
  • 2,229
  • 1
  • 17
  • 51
  • 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 Feb 01 '19 at 09:44

1 Answers1

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:

  • *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? – one-hand-octopus Feb 01 '19 at 10:33
  • 1
    @thinkvantagedu: correct (actually you can't really "sort the data in the database") –  Feb 01 '19 at 10:44