|
Home > Archive > MS SQL Server MSEQ > February 2006 > SELECT ~~ OVER(PARTITION BY ~~) in SQL Server 2005?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SELECT ~~ OVER(PARTITION BY ~~) in SQL Server 2005?
|
|
| Ed White 2006-02-25, 9:46 am |
| I'm using VS 2005 with SQL Server 2005 (all are release versions). I read
about the new Windows Functions SQL commands in a book, and, using the Query
Designer, created a SQL Query against my database that included
SELECT COUNT(field1) OVER (partition BY field1) AS Num
First, I type the query into the SQL pane. Then, when I click on another
pane, a message pops up "SQL Syntax Error Encountered...The OVER SQL
construct or statement is not supported." I hit OK, but when I click on
"Verify SQL Syntax", it responds "The SQL syntax has been verified against
the data source.", and when I Execute the query, it works and comes up with
results that would be expected if the OVER command works. So, does SQL
Server 2005 support the OVER command or not? If so, then the error statement
that pops up when I type it in is a bug.
--
Ed
| |
| David Portas 2006-02-25, 9:46 am |
| "Ed White" <ewhite@newsgroups.nospam> wrote in message
news:5AD5BA1A-688F-43F5-A08D- D02F94D5A2B0@microso
ft.com...
> I'm using VS 2005 with SQL Server 2005 (all are release versions). I read
> about the new Windows Functions SQL commands in a book, and, using the
> Query
> Designer, created a SQL Query against my database that included
>
> SELECT COUNT(field1) OVER (partition BY field1) AS Num
>
> First, I type the query into the SQL pane. Then, when I click on another
> pane, a message pops up "SQL Syntax Error Encountered...The OVER SQL
> construct or statement is not supported." I hit OK, but when I click on
> "Verify SQL Syntax", it responds "The SQL syntax has been verified against
> the data source.", and when I Execute the query, it works and comes up
> with
> results that would be expected if the OVER command works. So, does SQL
> Server 2005 support the OVER command or not? If so, then the error
> statement
> that pops up when I type it in is a bug.
> --
> Ed
Your syntax is correct but not all features can be supported by the
graphical Query Designer (same was true in 2000). That's just another good
reason not to use the Designer. Select File / New / Query and type your
queries in the editing window instead.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
| |
| Ed White 2006-02-25, 9:46 am |
| That explains a lot of things. When, in the designer, I tried to name parts
of the Query using "AS", it often didn't work as expected. You said use
"File / New / Query"--that option is not available in VS, so I assume you
mean use the SQL Server Management Studio to create queries?
--
Ed
"David Portas" wrote:
> "Ed White" <ewhite@newsgroups.nospam> wrote in message
> news:5AD5BA1A-688F-43F5-A08D- D02F94D5A2B0@microso
ft.com...
>
> Your syntax is correct but not all features can be supported by the
> graphical Query Designer (same was true in 2000). That's just another good
> reason not to use the Designer. Select File / New / Query and type your
> queries in the editing window instead.
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>
>
| |
| Wei Lu 2006-02-28, 8:30 pm |
| Hi Ed,
Yes, the option David provided is in the SQL Server Management Studio.
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
====================
====================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================
====================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
|
|
|
|
|