Sorting table data issue

Question

I've quite bulky data in a Database table and I want to sort the data based on their ID (Primary Key). The data in the key column could be:

001/2011,
002/2011,
001/2012

When I use 'order by id' it sorts the rows like

001/2011,
001/2012,
002/2011

However, what I am looking for is

001/2011,
002/2011,
001/2012

The data type of the id column is varchar(50). Is there a special SQL function that I should use to sort such type of data?

RichardTheKiwi · Accepted Answer · 2012-10-17 11:50:30Z

3

ORDER BY RIGHT(ID,4)+LEFT(ID,3)

This rearranges the varchar data so that the year comes first and the sequence/month/day-of-year comes after.

If you have some other format to your data, then think along the same lines. Shift the string around using SUBSTRING, of which LEFT and RIGHT are just 2 specific versions.

edited Oct 17, 2012 at 11:50

answered Oct 17, 2012 at 11:44

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

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

4 Comments

MatBailie Over a year ago

Although exceptionally picky, personally I'd replace the + with a ,. And/Or checking the position of the / incase it moves. ;) [+1 since it still works.]

RichardTheKiwi Over a year ago

And/Or checking the position of the / incase it moves. I'm not going down that spiral. The data is either coherent as shown in question, or we can crystal ball gaze all day.

aby Over a year ago

It works on the query but doesn't work when i put it in a view

RichardTheKiwi Over a year ago

@user ORDER BY in a does not work. You cannot force ORDER BY in a view or Table Function. See here as well: stackoverflow.com/a/1306426/573261

Collectives™ on Stack Overflow

Sorting table data issue

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related