i have this line in my query:
case When (h2.stkhst10wk - h2.stkhstClose) >= 0 then 'Below' else 'Above'
end as tenBeat

i need to sort by this value.

of course order by tenBeat will not work,
and tried   order by ((h2.stkhst10wk - h2.stkhstClose) >= 0)

any ideas would be appreciated,
thanks
kes

Either use the entire CASE WHEN...statement in your ORDER BY clause, or
wrap the query in subquery, and then order by tenBeat, like so:


SELECT tenbeat
FROM (SELECT tenbeat = CASE...END
      FROM foo) f
ORDER BY tenbeat

HTH,
Stu
Stu

Kes,

If the expression you show is part of your SELECT clause, you can in fact
ORDER BY tenBeat.  The ORDER BY clause applies to the result set
generated by the SELECT query that precedes it, which in your case
includes a column named tenBeat.

Steve Kass
Drew University

WebBuilder451 wrote:

Show quoteHide quote

CREATE TABLE tenbeat (
  tenbeatcol1 int,
  tenbeatcol2 int
)


INSERT INTO tenbeat VALUES(3,4)
INSERT INTO tenbeat VALUES(1,2)
INSERT INTO tenbeat VALUES(0,0)

SELECT *,tenbeatcol1+tenbeatcol2 FROM tenbeat
ORDER BY tenbeatcol1+tenbeatcol2

Hi
You can use a calculated column in ORDER BY clause as
SELECT *,tenbeatcol1+tenbeatcol2 AS calc FROM tenbeat
ORDER BY calc




Show quoteHide quote

THank's everyone for responding,
I agree it should work, but when i use the case statement it does not. If i
just specify the calculated field it's ok. Here is my query and the mystery.
If i kill the case and just use order by tenBeat it works ok, but the case
......is a case ........

declare @thsDate1 as INT
declare @thsDate2 as INT
declare @thsUnvID as INT
declare @thsSrt as varchar(10)
select @thsDate1 = 20050830
select @thsDate2 = 20050831
select @thsUnvID = 57001
select @thsSrt = 'Sym'

select
h1.stkhstcsisym,
s.csistksym1,
s.csistkCompany,
s.csistkExchange as Exchange,
case when h2.stkhstBuySell = '' THEN 'N/A' ELSE h2.stkhstBuySell END as
PFBuySell,
CASE
    WHEN h2.stkhstBuySell = 'B' and h1.stkhstBuySell = 'S' THEN 'gnBK'
    WHEN h2.stkhstBuySell = 'S' and h1.stkhstBuySell = 'B' THEN 'rdBK'
ELSE 'wtBK'
END as NEWPFBuySell,       
h2.stkhstXO,
CASE
    WHEN h2.stkhstXO = 'X' and h1.stkhstxo = 'O' THEN 'gnBK'
    WHEN h2.stkhstXO = 'O' and h1.stkhstxo = 'X' THEN 'rdBK'
    ELSE 'wtBK'
END as NEWPFXO,
CASE
    WHEN h2.stkhstLine = 'A' AND h1.stkhstLine = 'B' THEN 'gnBK'
    WHEN h2.stkhstLine = 'B' AND h1.stkhstLine = 'A' THEN 'rdBK'
    ELSE 'wtBK'
END as NEWPFtrend,
h2.stkhstRSBS,
CASE
    WHEN h2.stkhstRSBS = 'B' AND h1.stkhstRSBS = 'S' THEN 'gnBK'
    WHEN h2.stkhstRSBS = 'S' AND h1.stkhstRSBS = 'B' THEN 'rdBK'
    ELSE 'wtBK'
END as NEWRSBuySell,
h2.stkhstRSXO,
CASE
    WHEN h2.stkhstRSXO = 'X' AND h1.stkhstRSXO = 'O' THEN 'gnBK'
    WHEN h2.stkhstRSXO = 'O' AND h1.stkhstRSXO = 'X' THEN 'rdBK'
    ELSE 'wtBK'
END as NEWRSXO,
case When (h2.stkhst10wk - h2.stkhstClose) >= 0 then 'Below' else 'Above'
end as tenBeat,
CASE
    WHEN  ((h2.stkhst10wk - h2.stkhstClose) >= 0) AND ((h1.stkhst10wk -
h1.stkhstClose) < 0) then 'gnBK'
    WHEN  ((h1.stkhst10wk - h1.stkhstClose) >= 0) AND ((h2.stkhst10wk -
h2.stkhstClose) < 0) then 'rdBK'
    ELSE 'wtBK'
END AS NEWtenBeat,
h2.stkhst10Wk,
h2.stkhstClose,
n.unvName
/*
h1.stkhstBuySell,
i1.idxhstBuy,
i2.idxhstBuy,
*/
from stkhst h1 join stkhst h2 on h2.stkhstcsisym = h1.stkhstcsisym
join unvmem u on u.unvmemcsiid = h1.stkhstcsisym
join csistk s on s.csistkcsisym = h1.stkhstcsisym
join idxhst i1 on i1.idxhstidxid = @thsUnvID
join idxhst i2 on i2.idxhstidxid = @thsUnvID
join unv n on n.unvID = u.unvmemunvid
where u.unvmemunvid = @thsUnvID
and h1.stkhstdate = @thsDate1
and h2.stkhstdate = @thsDate2
and i1.idxhstDate = @thsDate1
and i2.idxhstDate = @thsDate2
order by
CASE @thsSrt
    WHEN  'Exchange' THEN s.csistkexchange
    WHEN 'Company' THEN s.csiStkCompany
    WHEN 'PFSig' THEN h2.stkhstBuySell
    WHEN 'PFXO' THEN h2.stkhstXO
    WHEN 'PFTrend' THEN h2.stkhstLine
    WHEN 'RSSig' THEN h2.stkhstRSBS
    WHEN 'RSXO' THEN h2.stkhstRSXO
   WHEN 'tenWK' THEN tenBeat <------ here it says invalid column name

    ELSE s.csistksym1
END
Show quoteHide quote

Trouble with SP
SQLDataAdapter.FillSchema() hangs with select on view
Simplifying CHECK constraint
SP plan different from batch
how to rename a temp table