CODEWITHSUNDEEP
sqlsql-server-2008sorting
aby
aby

Reputation: 830

Sorting table data issue

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?

Upvotes: 0

Views: 91

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107826

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.

Upvotes: 3

Related Questions