help with query, wierd error - SQL Server Forums

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)

help with query, wierd error

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-06-07 : 19:40:10

I'm just modifying the ORDER BY clause on a pre-existing working query and getting the following error:

Server: Msg 207, Level 16, State 3, Procedure select_Media_FrontPage_TopRated, Line 8
Invalid column name 'votes'.

When I order by VOTES or POINTS on their own, rather than (points/votes) the query works fine. Is this a hint to any possible problem?

Thanks once again! :)
mike123

alter PROCEDURE dbo.select_Media_FrontPage_TopRated

AS

SET NOCOUNT ON

SELECT
M.mediaID,
pageName,
mediaTitle,
mediaDesc,
datePosted,
COUNT(UV.mediaID) AS votes,
SUM (
CASE
WHEN points IS NULL
THEN 0
ELSE points
END ) as points
FROM
tblMedia M
LEFT OUTER JOIN tblUserVote UV ON UV.mediaID = M.mediaID

WHERE m.activeStatus ='1'

GROUP BY

M.mediaID,
pageName,
mediaTitle,
mediaDesc,
datePosted

ORDER BY
(points / votes) DESC

GO

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-06-07 : 19:43:02

Votes is a calcuation, not an actual column, so it cannot participate in another calculation. Either replace it in the ORDER BY with Count(UV.mediaID) or wrap the whole thing in a subquery, then put the ORDER BY outside of it.

Don't ask me why it worked before, it shouldn't have.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-06-07 : 20:11:31

Hey Rob thanks for the hand.

I added what you said

Count(UV.mediaID)

Server: Msg 8127, Level 16, State 1, Procedure select_Media_FrontPage_TopRated, Line 8
Column name 'UV.points' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

I got this error and then after I fix what it recommends my query ends up bringing back 258 rows instead of the 40 it should. It's doing this because of something screwy in the groupby clause I believe.

I'd like to try your subquery method but I haven't done one in so long I'm not sure I understand the approach on this one. One other thing that I realized I needed to add a simple "WHERE VOTES > 5" , simple obviously in most cases but it was causing me complications in this one.

Thanks very much once again!
mike123

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-06-07 : 20:22:21

This should do it:

alter PROCEDURE dbo.select_Media_FrontPage_TopRated

AS

SET NOCOUNT ON

SELECT * FROM (
SELECT
M.mediaID,
pageName,
mediaTitle,
mediaDesc,
datePosted,
COUNT(UV.mediaID) AS votes,
SUM (
CASE
WHEN points IS NULL
THEN 0
ELSE points
END ) as points
FROM
tblMedia M
LEFT OUTER JOIN tblUserVote UV ON UV.mediaID = M.mediaID

WHERE m.activeStatus ='1'

GROUP BY

M.mediaID,
pageName,
mediaTitle,
mediaDesc,
datePosted ) a

ORDER BY
(a.points / a.votes) DESC

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-06-07 : 22:51:25

thanks alot rob! ..

just one quick question, is it ok to do SELECT *'s on subquery's ? Or are you just writing it quickly and I should know better? :)

thx!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-06-07 : 23:49:48

There was some debate about that recently:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50761

I don't have a preference either way, I don't think SELECT * will cause you any problems here.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources