query to show even and odd order

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  March 27, 2009 at 5:11 pm

  #967799

  andrewd.smith (3/27/2009)

  ---

  I'm a littel disappointed that no one wanted to play.

  I think this is in the spirit of that thread. I'm quite pleased to have found a new use for the SOUNDEX function, and the ORDER BY clause uses a single expression to corral the rows in the order required by the OP.

  SELECT T.N, Parity = LEFT(SOUNDEX(CHAR(69 + 10 * (T.N % 2))), 1)

  FROM Tally T WHERE T.N BETWEEN 1 AND 10

  ORDER BY T.N - COS(PI() * T.N)

  [font="Courier New"]N Parity

  2 E

  1 O

  4 E

  3 O

  6 E

  5 O

  8 E

  7 O

  10 E

  9 O[/font]

  Nice one!