Trouble with nested query limited by maximum rank

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Trouble with nested query limited by maximum rank

Author

Topic

ThomasJacobs
Starting Member

2 Posts

Posted - 2006-08-11 : 11:42:13

I am trying to implement the following nested query to rank by a change in time value for a given starting period date and period length. The code:

SELECT TV0.ID, TV0.Weeks, TV0.StartPeriod,
TV0.TimeValChangeOverAmort,"Hi" as Type,

(SELECT COUNT(DISTINCT TimeValChangeOverAmort) FROM
tblCloseToMoneyObsPeriodChanges as TV1
WHERE ( TV1.TimeValChangeOverAmort >= TV0.TimeValChangeOverAmort)
AND (TV1.Weeks=TV0.Weeks)
AND (TV1.StartPeriod=TV0.StartPeriod)) AS Rank
FROM tblCloseToMoneyObsPeriodChanges as TV0
WHERE Rank <= 100;

Generates the following error:

Server: Msg 207, Level 16, State 3, Line 3
Invalid column name 'Rank'.

However, if I simply delete the last line and end with the final FROM statement I get correctly ranked results with no cut off at 100. Is there some way to make this work without a second query? Thanks.

Thomas Jacobs

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-08-13 : 23:08:59

The alias doesn't apply for anything other than naming the column for the final output. You'll need to
a) Replace the WHERE Rank <= 100 with WHERE (copy of code) <= 100
b) Wrap it into a sub select

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-13 : 23:46:53

select * from
(
SELECT TV0.ID, TV0.Weeks, TV0.StartPeriod,
TV0.TimeValChangeOverAmort,"Hi" as Type,
(SELECT COUNT(DISTINCT TimeValChangeOverAmort) FROM
tblCloseToMoneyObsPeriodChanges as TV1
WHERE ( TV1.TimeValChangeOverAmort >= TV0.TimeValChangeOverAmort)
AND (TV1.Weeks=TV0.Weeks)
AND (TV1.StartPeriod=TV0.StartPeriod)) AS Rank
FROM tblCloseToMoneyObsPeriodChanges as TV0
) a
WHERE Rank <= 100

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ThomasJacobs
Starting Member

2 Posts

Posted - 2006-08-14 : 22:36:55

Thanks for the replies.

nr: While I see what you are doing with the outer select I am uncertain what the "a" you have on the line preceding Where Rank <= 100 signifies. Would you mind sharing a little more? Also, from a performance perspective would it be better to simply create a temporary table and then do a simple select with a second query on the size of rank vs. the now sub - sub query as you have specified or is there no difference? Thanks again.

Thomas Jacobs

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-08-15 : 04:04:09

"a" just names the sub select. Call it whatever you want but it needs to be there so you can reference the query as a table.
I doubt you'll get better performance with temp tables. Often (but not always) a single query is the way to go. I'm not sure if I get your question but in this case I can't see what you'd get out of doing it a different way as you need the count in the predicate. No way of escaping that fact. You can try it though - get some execution plans & see what falls out and do some timings.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources