How to sort a table by a column name permanently in DB2

Question

I want to sort a table 'Employee' by a column name 'EmpID' and have those changes persistent.

Here is my Employee Table. 'EmpID' is the primary Key.

EmpID    EmpName  City

Ram       234     HYD   
Shyam     130     BLR 
Madan     894     KAN 
Ramesh    101     CHN

So I wrote a a sql query

Select * FROM Employee ORDER BY EmpID ASC;   


        EmpID    EmpName  City

        Ramesh    101     CHN 
        Shyam     130     BLR
        Ram       234     HYD  
        Madan     894     KAN

but it only produces the resultSet in sorted format but the real table doesn't get change permanently.

Now when i run Select * FROM Employee, I get the original table again and not the updated table.

Select * FROM Employee;

            EmpID    EmpName  City

            Ram       234     HYD   
            Shyam     130     BLR 
            Madan     894     KAN 
            Ramesh    101     CHN

so how this can be done ?

@EstebanP.: It's a requirement. I am running some operations on that table for that I want to have that table in sorted state every time i perform an insert. I want to find out the row having max. employee Id before insertion. I am also doing random insertions and deletions. — Krishna Tiwari, Apr 08 '19 at 07:08
See this question and answers [SQL best practice to deal with default sort order ](https://stackoverflow.com/a/1793161/9602361). — Tekcins, Apr 08 '19 at 07:12
`I want to find out the row having max. employee Id before insertion. I am also doing random insertions and deletions.` Why? If you're doing this to find the "next available id", that means you need to lock the entire table from the read query until the `INSERT` completes, or you risk duplicate rows (hopefully you have a unique key on the column). I'm also suspicious about deleting employee records, as opposed to making them inactive or something - just because they might not work for you, doesn't mean you no longer have a relationship — Clockwork-Muse, Apr 08 '19 at 17:00
@Clockwork-Muse : I need to delete the records as i am testing the scalability of database along with multi threading. Anyways finding out the max and inserting a new record with max+1 solves my problem. Closing the Thread. Thank you. — Krishna Tiwari, Apr 09 '19 at 07:28

score 0 · Answer 1 · answered Apr 08 '19 at 07:14

I think you're missing some basic fundamentals of database principles here. A storage engine doesn't really has a concept of ordered records, it just stores data based on insertion order and writes data to disk how it sees best fit.

You 'could' try to persist the data by going over your resultset and insert the data into a new table row by row, but even then your order wouldn't be guaranteed. And this would mean you would have to repeat this operation every time you would add/delete a record.

There really is no legitimate reason to want to manipulate how the data is persisted. If you have operations running on that table relying on the data being ordered by empId, you need to write your operations in such a way that they retrieve the data with an order by clause.

I did, hence my last paragraph. It is not a legite requirement as it goes against the fundamentals of a database. You do not have control over how the engine stores your data. You can only control how you want to retrieve that data using SQL. If you want to find out the max employee ID then just use 'SELECT MAX(empId) FROM Employee'. — TheWhiteRabbit, Apr 08 '19 at 08:49

How to sort a table by a column name permanently in DB2

1 Answers1