sql - How to get the Highest, then the lowest, then the 2nd higest, and then the 2nd lowest value and so on from a table -

i've question, how can highest value, lowest value, second highest value table.

for example: in table

name        value ---------------------- apple        2 pear         3 pineapple    6 mango        7 kiwi         1 

result should this:

name           value ----------------------- mango           7 kiwi            1 pineapple       6 apple           2 pear            3 

thanks!

i'm assuming tsqlt tag meant tsql, , further implies sql server:

;with numbered (     select name,value,         row_number() on (order value desc) rndesc,         row_number() on (order value asc) rnasc             @t ), mixednumbered (     select         name,value,         case when rndesc < rnasc rndesc else rnasc end rnfin,         rnasc,         rndesc             numbered ) select name,value mixednumbered order rnfin,rndesc 

this works finding row numbers whilst considering list sorted both highest-to-lowest , lowest-to-highest (in numbered, rndesc , rnasc). take lowest row number achieved when considering either of these orderings (mixednumbered, rnfin).

this should then, produce 2 rows rnfin equal 1, 2 rows equal 2, , on; pairing nth highest , nth lowest rows until reach middle of set.

we use sort final result set - use position obtained considering values sorted highest-to-lowest (rndesc) tie breaker between each pair of rows same rnfin value. means, each pair, higher valued row appear first.

to reverse result (lowest first, highest, second lowest, second highest, etc), need change final order by clause rnfin,rnasc.