SQL Order By: you may need it and not realize it

Picture from w3resource.com

Sometimes you need to use the ORDER BY clause in SQL, even if you didn’t expect to use it.

Suppose you run this query:

SELECT * FROM Users

The query will usually return the rows back alphabetically- perhaps by the Primary ID (especially if that’s the first column in your dataset).

And of course you can explicitly state a column to sort by:

SELECT * FROM Users ORDER BY last_name

And quite often, the query is “smart enough” to return data in alphabetical order, based on the columns selected:

SELECT first_name, last_name, id FROM users

But I recently learned the query results sort order will not be returned in a guaranteed order unless you explicitly specify it.

I needed to copy data from my production database into my development database. I used this SQL:

INSERT INTO devDb.dbo.MyTable (field1, field2...)
   SELECT field1, field2... FROM prodDb.dbo.myTable

I happen to be using SQL Server, where I created a Linked Server between databases on two different servers. This allows me to use this SQL syntax.

My table was set up with an auto-incrementing integer as the Primary Key. I knew that the higher the number was, the more recent the row was inserted into my database.

It was possible for users to enter in duplicative data into my database, and I wanted to get the “latest” value. I used this SQL:

SELECT employeeId, MAX(id) AS maxID
FROM myTable
GROUP BY employeeId

Now that I had which row to use, I linked that in with another query to get the fields I needed:

SELECT C.employeeId, C.comments
FROM tableWithComments C
INNER JOIN (
   SELECT employeeId, MAX(id) AS maxID
   FROM myTable
   GROUP BY employeeId
) AS qryMax ON
    C.id = qryMax.maxID
AND C.employeeId = qryMax.employeeId

However when I ran my queries in the development database, I wasn’t getting the expected results.

The problem was with the initial SELECT statement, that was used in the INSERT statement to populate the table: it didn’t put in the rows in the expected sequence!

The SELECT statement didn’t explicitly have a sort order, and rows “at the bottom” of the expected dataset were put in somewhere in the middle!

Since I was looking for the Max ID, the wrong data was being returned (some other data had an ID that was of higher value).

The solution was to explicitly specify the sort order in my SELECT query, to ensure that data was INSERTed into the database in the expected order.

I simply added an ORDER BY statement to the query that was appending data into development from production:

INSERT INTO devDb.dbo.MyTable (field1, field2...)
   SELECT field1, field2... FROM prodDb.dbo.myTable
   ORDER BY ID

Once I did this, I was able to run the query in the development database and got the expected results.

SQL Lesson Learned

If you don’t explicitly specify an ORDER BY clause, then you may not get the data results in the order you expect. This could be problematic if you’re relying on data that has auto-incrementing numbers.