Re: SELECT ~~ OVER(PARTITION BY ~~) in SQL Server 2005?

"Ed White" <ewhite@xxxxxxxxxxxxxxxxx> wrote in message
news:5AD5BA1A-688F-43F5-A08D-D02F94D5A2B0@xxxxxxxxxxxxxxxx
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
--


.

Relevant Pages

  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: trouble with SQL syntax
    ... Dim mySQL As String ... and created your query in the query designer? ... designer, save it in the MDB, and then just open the query from VB by ... name instead of using an SQL statement. ...
    (microsoft.public.vb.general.discussion)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- table structure?
    ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... I have one report complete, ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)