| Author |
 Topic  |
|
|
mike123
Flowing Fount of Yak Knowledge
1462 Posts |
 Posted - 06/07/2005 : 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
USA
15557 Posts |
Posted - 06/07/2005 : 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
Flowing Fount of Yak Knowledge
1462 Posts |
Posted - 06/07/2005 : 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
USA
15557 Posts |
Posted - 06/07/2005 : 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
Flowing Fount of Yak Knowledge
1462 Posts |
Posted - 06/07/2005 : 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
USA
15557 Posts |
|
| |
Topic |
|
|
|
help with query, wierd error
