Order by in SQL Server 2008

Question

I have an issue with my query.

I need to sort my records based on the Created date and Image position columns.

Where as I have the table as below

'---------------------------------------------------------
Id | Image Type |Created Dt | Image Position |
'---------------------------------------------------------
1*|Original Image |2013-11-20 17:27:06.380 | 1 |
2*|Original Image |2013-11-20 17:27:07.380 | 2 |
3*|*Blank_Image *|2013-11-20 17:27:08.380 | 0 |
4*|Original Image |2013-11-20 17:27:09.380 | 3 |
'---------------------------------------------------------

Now if I use

Order by [Created dt], [Image Position] Then I am getting proper

Now I want to change the image position of imge id 4 to image position 1 and image id 1 to image position 3

'---------------------------------------------------------
Id | Image Type |Created Dt | Image Position |
'---------------------------------------------------------
1*|Original Image |2013-11-20 17:27:06.380 | 3 |
2*|Original Image |2013-11-20 17:27:07.380 | 2 |
3*|*Blank_Image *|2013-11-20 17:27:08.380 | 0 |
4*|Original Image |2013-11-20 17:27:09.380 | 1 |
'---------------------------------------------------------

Now my expected output should be

'---------------------------------------------------------
Id | Image Type |Created Dt | Image Position |
'---------------------------------------------------------
4*|Original Image |2013-11-20 17:27:09.380 | 1 |
2*|Original Image |2013-11-20 17:27:07.380 | 2 |
3*|*Blank_Image *|2013-11-20 17:27:08.380 | 0 |
1*|Original Image |2013-11-20 17:27:06.380 | 3 |
'---------------------------------------------------------

But here in this case, if I am using

Order by [Created dt], [Image Position] it is giving me

'---------------------------------------------------------
Id | Image Type |Created Dt | Image Position |
'---------------------------------------------------------
1*|Original Image |2013-11-20 17:27:06.380 | 3 |
2*|Original Image |2013-11-20 17:27:07.380 | 2 |
3*|*Blank_Image *|2013-11-20 17:27:08.380 | 0 |
4*|Original Image |2013-11-20 17:27:09.380 | 1 |
'---------------------------------------------------------

I am not able to explain this more than this.. If someone can understand this, please help me out.

Thanks in advance.

Answer 1

If I understand correctly you need:

order by [Created dt] DESC, [Image Position]

Answer 2

Since you are sorting on CreatedDate first and then ImagePosition - this is the expected behavior. The second sorting field (ImagePosition) will only be used as a tiebreaker where multiple rows for the same CreatedDate are found. In your case, all CreatedDate's are different, and so the ImagePosition sorting is never used (or needed).

Try changing your sort order to get what you want:

Order by [Image Position], [Created dt]