I've been puzzling over this problem for days now, and have just identified the source of my woes - an order by clause is not working as expected.
The script goes like this:
select * from my_table order by change_effective_date, unique_id desc
change_effective_date is a datetime field, and unique_id is an int field.
I had expected this to give me the most recent row first (i.e. the row with the highest value in change_effective_date). However, it was giving the oldest row first, and the unique_id was also in ascending order (these IDs are normally sequential, so I would generally expect them to follow the same order as the dates anyway, though this is not completely reliable).
Puzzled, I turned to Google and found that data type precedence can affect order by clauses, with lower-ranking datatypes being converted to the higher-ranking datatype: https://blog.sqlauthority.com/2010/10/08/sql-server-simple-explanation-of-data-type-precedence/
However, datetime takes precedence over int, so it shouldn't be affected in this way.
More curiously, if I take unique_id out of the order by clause, it sorts the data in descending date order perfectly. I do want to add a unique identifier to the order by clause, though, as there could be multiple rows with the same date and further on in the script I want to identify the most recent (in this case, the unique_id would be the tie-breaker as I would assume it to be sequential).
If anyone can explain what's happening here, I'd really appreciate it!
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
SELECT * FROM my_table ORDER BY my_table.change_effective_date, my_table.unique_id DESC