November 1, 2008 at 3:14 am
hi
why this raise error ?
SELECT * FROM
(
SELECT *,ROW_NUMBER() OVER(ORDER BY Name DESC)AS 'RowNumber' FROM Galery
)myTable
SELECT MAX(RowNumber) FROM myTable
OR this one :
SELECT*FROM
(
SELECT *,ROW_NUMBER() OVER(ORDER BY Name DESC)AS 'RowNumber' FROM Galery
)myTable WHERE MAX(RowNumber)<10
November 1, 2008 at 3:31 am
The first one throws an error because the last select refers to a table that doesn't exist. The derived table myTable is just a named subquery and it doesn't persist after the select that it's defined in ends.
The second is complaining about the aggregate. If you want to filter on aggregates, you use the HAVING clause, not the WHERE clause. You'll also need either a group by, or aggregates on the other fields you're returning
The whole query's meaningless though. What are you trying to do?
p.s. Appropriate forum please. Rownumber is a SQL 2005 feature. Don't post SQL 2005-related questions in the SQL 2000 forums
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 1, 2008 at 3:42 am
i need to select a record base on a RowNumber,besides i need to check and return a value that shows whethere this record is or is not the last record !!
November 1, 2008 at 3:54 am
Continued here - http://qa.sqlservercentral.com/Forums/Topic595406-8-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply