I have the following table
DBName Server Status UpdateTime DB1 server1 NULL 5/4/2019 DB1 server1 NULL NULL DB2 server2 NULL 6/4/2019 DB2 server2 NULL 6/4/2019 DB3 server2 NULL NULL DB3 server2 NULL NULL
Sometimes, when I click select rows, it somehow rearranges some random rows. also if I click edit I notice the rows have been rearranged. Why does that happen?
DBName Server Status UpdateTime DB1 server1 NULL 5/4/2019 DB2 server2 NULL 6/4/2019 DB3 server2 NULL NULL DB3 server2 NULL NULL DB2 server2 NULL 6/4/2019 DB1 server1 NULL NULL
I mean this doesn’t really cause problems but I have 3 environments and it makes it difficult to compare the results across environments when the rows aren’t in the same order.
At first it happened while I was working remotely from home. so I thought it could be VPN/network issue. But even at the office, it happened, where I’m not connected to VPN.
EDIT:
This is different from this question as I dont have a primary key
Does ‘Select’ always order by primary key?
Advertisement
Answer
SQL tables have no inherent ordering. This is by design. If you don’t specify the order then the DBMS is free to return the rows in whatever order it sees fit. This often turns out to be the order in which the rows were originally inserted, and so people sometimes assume that is is the natural or inherent order of the table. But in fact there is nothing which guarantees this, so you cannot rely on it. Occasionally, as you’ve noticed, the DBMS may decide to do things differently, for its own reasons.
If you want to see things in a specific order consistently then fortunately the solution is clear and easy: you must use an ORDER BY clause in your SELECT query.
If you need to order things in a non-natural way (e.g. ordering a varchar column non-alphabetically) then a standard workaround is to add an extra “sort” column as a proxy, and order by that instead.