'staggered' sort

• ron5

  Old Hand

  Points: 306

  More actions

  May 15, 2011 at 9:03 am

  #389529

  i'm wondering if anyone knows of a simple trick in achieving a 'staggered' sort.

  i requre the values to be returned in order of first the highest Value, then the lowest Value,

  followed by 2nd-Highest value then the 2nd Lowest value

  ie:

  100

  1

  99

  2

  98

  3

  My data is:

  CREATE TABLE A_Table (ProdCode varchar(50),

  Value int)

  INSERT INTO A_Table (ProdCode,Value) VALUES ('AC',100)

  INSERT INTO A_Table (ProdCode,Value) VALUES ('JV',99)

  INSERT INTO A_Table (ProdCode,Value) VALUES ('ZR',98)

  INSERT INTO A_Table (ProdCode,Value) VALUES ('ZR',2)

  INSERT INTO A_Table (ProdCode,Value) VALUES ('OE',1)

  i could split the all the into two separate work-tables then return the rquired results by alternating reading between

  the two work tables, but its quite a long-winded solution .

  Thanks

  Ron

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  May 15, 2011 at 12:06 pm

  #1324740

  Few questions here :

  1. How big a table is yours ?

  2. Do u have IDENTITY column or any "numbered" column that can uniquely find a row in your table ?

  This kinda "ordering" can be done by simple math, i believe, but i need answers for those questions 🙂

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  May 15, 2011 at 12:17 pm

  #1324743

  ron5 (5/15/2011)

  ---

  i'm wondering if anyone knows of a simple trick in achieving a 'staggered' sort.

  i requre the values to be returned in order of first the highest Value, then the lowest Value,

  followed by 2nd-Highest value then the 2nd Lowest value

  ie:

  100

  1

  99

  2

  98

  3

  My data is:

  CREATE TABLE A_Table (ProdCode varchar(50),

  Value int)

  INSERT INTO A_Table (ProdCode,Value) VALUES ('AC',100)

  INSERT INTO A_Table (ProdCode,Value) VALUES ('JV',99)

  INSERT INTO A_Table (ProdCode,Value) VALUES ('ZR',98)

  INSERT INTO A_Table (ProdCode,Value) VALUES ('ZR',2)

  INSERT INTO A_Table (ProdCode,Value) VALUES ('OE',1)

  i could split the all the into two separate work-tables then return the rquired results by alternating reading between

  the two work tables, but its quite a long-winded solution .

  Thanks

  Ron

  Not saying it is great code , but this works for the test data - there may be some tweaking required to be more robust.

  ;with data1 as

  (

  SELECT ProdCode,Value,convert(float,count(*) over(partition by @@spid)+1) as total_count,row_number() over (order by Value desc) as rownum

  from A_TABLE

  )

  select ProdCode,Value

  from data1

  order by abs(rownum-(total_count/2)) DESC,Value desc

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]