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.

What is it you actually want to do with this. You desired data seems not be sorted by date nor by image position? — Mithrandir
– Mithrandir, Commented Nov 20, 2013 at 15:22
I want to the all original images to be sorted based on image position but at the same time, I want all Blank images should be sorted on created dt. Will this be possible? Sorry this is little complicated. — k.chinni66
– k.chinni66, Commented Nov 20, 2013 at 15:34
I don't think what you want can be done by simply ordering by date and position. — Mithrandir
– Mithrandir, Commented Nov 20, 2013 at 16:02

PinnyM · Accepted Answer · 2013-11-20 15:14:36Z

3

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]

answered Nov 20, 2013 at 15:14

PinnyM

35.6k4 gold badges77 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

k.chinni66 Over a year ago

I want all blank pages without any sorting and remaining original pages with sorting. If a blank page have position as 3 even after sorting the position should be 3.

PinnyM Over a year ago

To clarify, the reason the query worked for you initially is not because of the ImagePosition (since that was being ignored), but rather because the CreatedDt's happen to occur in the order you anticipated. The only criteria being used here is CreatedDt - if you want to add other criteria, you can do so - but that won't leave any of your rows unsorted, nor can you ever do this using an ORDER BY statement. You can simulate what you want (unsorted rows among sorted rows) if you have some hard logic to put in place, but it will look fairly messy by the time you are done.

vkamayiannis · Accepted Answer · 2013-11-20 15:14:46Z

0

If I understand correctly you need:

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

answered Nov 20, 2013 at 15:14

vkamayiannis

7353 silver badges13 bronze badges

3 Comments

PinnyM Over a year ago

No, that's not the problem at all - it would also still give an undesired result.

vkamayiannis Over a year ago

Yes you're right I didn't undesrstand it correctly. I just saw that the row that has "17:27:07.380" is above "17:27:07.380". My answer is incorrect

k.chinni66 Over a year ago

I want all blank pages without any sorting and remaining original pages with sorting. If a blank page have position as 3 even after sorting the position should be 3.

Collectives™ on Stack Overflow

Order by in SQL Server 2008

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related