0

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
  • 105,798
  • 26
  • 196
  • 262
aby
  • 810
  • 6
  • 21
  • 36

1 Answers1

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.

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • 1
    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.]* – MatBailie Oct 17 '12 at 11:46
  • 1
    `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. – RichardTheKiwi Oct 17 '12 at 11:48
  • It works on the query but doesn't work when i put it in a view – aby Oct 19 '12 at 14:04
  • @user ORDER BY in a **does not** work. You cannot force `ORDER BY` in a view or Table Function. See here as well: http://stackoverflow.com/a/1306426/573261 – RichardTheKiwi Oct 19 '12 at 21:05