Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
pkovarik
Starting Member
6 Posts |
Posted - 2014-02-26 : 17:11:39
|
Please help me to correct error. I have queryWITH TGrpAS(SELECT timeon,timeoff,ROW_NUMBER() OVER (PARTITION BY id ORDER BY timeon DESC) AS rnFROM AlarmsWHERE timeon>='2014.02.19 22:33:45.000' AND timeon<='2014.02.26 22:33:45.000')Select ROW_NUMBER() OVER (ORDER BY timeon) as x, * FROM Tgrp WHERE (rn=1) and (x between 6009 and 6051)but I've received the error messsage "Invalid column name 'x'."If I'll remove last condition (x between 6009 and 6051) it's ok. But I need to maintain this condition for the range selection. What is the correct syntax to eliminate error message? |
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-02-26 : 17:31:31
|
WITH TGrpAS(SELECT timeon,timeoff,ROW_NUMBER() OVER (PARTITION BY id ORDER BY timeon DESC) AS rnFROM AlarmsWHERE timeon>='2014.02.19 22:33:45.000' AND timeon<='2014.02.26 22:33:45.000')select * from (Select ROW_NUMBER() OVER (ORDER BY timeon) as x, * FROM Tgrp WHERE (rn=1)) as a where x between 6009 and 6051 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-02-27 : 02:33:22
|
quote: Originally posted by pkovarik Please help me to correct error. I have queryWITH TGrpAS(SELECT timeon,timeoff,ROW_NUMBER() OVER (PARTITION BY id ORDER BY timeon DESC) AS rnFROM AlarmsWHERE timeon>='2014.02.19 22:33:45.000' AND timeon<='2014.02.26 22:33:45.000')Select ROW_NUMBER() OVER (ORDER BY timeon) as x, * FROM Tgrp WHERE (rn=1) and (x between 6009 and 6051)but I've received the error messsage "Invalid column name 'x'."If I'll remove last condition (x between 6009 and 6051) it's ok. But I need to maintain this condition for the range selection. What is the correct syntax to eliminate error message?
The reason is x is computed column at the same level so you cant use alias in where directlymake it into a derived table as per sugestion provided and then you'll be able to use this column in filter------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|