0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for programmers

Create table #table1 (Trade_Date date, col_name varchar(100), col_value varchar(100), rec_count int)
Insert into #table1
   (TRADE_DATE, COL_NAME, COL_VALUE, REC_COUNT)
 Values
   (('12/08/2011'), 'STOCK_SYM', 'A', 1210885);
Insert into #table1
   (TRADE_DATE, COL_NAME, COL_VALUE, REC_COUNT)
 Values
   (('12/08/2011'), 'STOCK_SYM', 'AA', 1211091);
Insert into #table1
   (TRADE_DATE, COL_NAME, COL_VALUE, REC_COUNT)
 Values
   (('12/08/2011'), 'STOCK_SYM', 'AAAA', 9);
Insert into #table1
   (TRADE_DATE, COL_NAME, COL_VALUE, REC_COUNT)
 Values
   (('12/08/2011'), 'STOCK_SYM', 'AAAE', 1);
Insert into #table1
   (TRADE_DATE, COL_NAME, COL_VALUE, REC_COUNT)
 Values
   (('12/08/2011'), 'STOCK_SYM', 'AABVF', 11);
Insert into #table1
   (TRADE_DATE, COL_NAME, COL_VALUE, REC_COUNT)
 Values
   (('12/08/2011'), 'STOCK_SYM', 'AAC', 916);
Insert into #table1
   (TRADE_DATE, COL_NAME, COL_VALUE, REC_COUNT)
 Values
   (('12/08/2011'), 'STOCK_SYM', 'AACAY', 2);
Insert into #table1
   (TRADE_DATE, COL_NAME, COL_VALUE, REC_COUNT)
 Values
   (('12/08/2011'), 'STOCK_SYM', 'AACC', 2469);
Insert into #table1
   (TRADE_DATE, COL_NAME, COL_VALUE, REC_COUNT)
 Values
   (('12/08/2011'), 'STOCK_SYM', 'AACOU', 66);
   Insert into #table1
   (TRADE_DATE, COL_NAME, COL_VALUE, REC_COUNT)
 Values
   (('12/08/2011'), 'STOCK_SYM', 'AACOU', 56)
  Insert into #table1
   (TRADE_DATE, COL_NAME, COL_VALUE, REC_COUNT)
 Values
   (('12/08/2011'), 'STOCK_SYM', 'FOO', 57);

          
   
   
WITH data as (   
   SELECT  
      row_number() over (order by rec_count desc) rn_desc,
      row_number() over (order by rec_count asc) rn_asc,
      trade_date,
      COL_NAME,
      col_value,
      rec_count
      
   FROM #table1) 
SELECT 
  d1.col_value col_val1,
  d1.rec_count max_val,
  d2.col_value col_val2,
  d2.rec_count min_val
FROM
  data d1
  INNER JOIN data d2
  ON d1.rn_desc = d2.rn_asc
WHERE 
  d1.rn_desc <= (select CEILING( cast(COUNT(*) as decimal (5,2))/2) FROM data)
   
  
   

fork query

created dec 19 11 conradfrix