Prev: SQL Server Profiler Determine Number Times User Account Used
Next: Speed Up OPENROWSET?
From: Rich on 1 Dec 2009 16:57
This sample does exactly what I need

Select yr, fName, grade,
Row_Number() Over (Partition By fName Order By yr) As Attempt
From #tmp1

note that I don't even need an identity column. The sample ddl is just
quicky test data to derive some Tsql to perform the desired operation.
Eventually, this will be a subquery of a larger query for reporting purposes.
The actual data table(s) meet relational design standards.

Do you think that making my data set into a fact table and using mdx queries
would be a better solution to my endeavor?





"--CELKO--" wrote:

> Your DDL will not work. IDENTITY table property is a non-relatioanl
> count of phsyical insertion attempts (not even successes) into
> physical storage. It is not even a column. If you use INSERT INTO
> Foobar VALUES (..), (..),.. (..); this attempt count is non-
> deterministic. Same thing with your old-style UNION ALL list. SQL
> does not work like a deck of punch cards or a mag tape.
>
> You have no key. You can never have a key, since all of the columns
> are NULL-able. What do those NULLs mean? Think about what VARCHAR(1)
> means. If you had a relational design, it might look like this:
>
> CREATE TABLE GradeBook
> (course_yr INTEGER NOT NULL,
> test_nbr INTEGER NOT NULL
> CHECK (test_nbr > 0),
> first_name VARCHAR (50) NOT NULL, -- you did research this size?
> course_grade CHAR(1) DEFAULT 'F' NOT NULL
> CHECK (course_grade IN ('P', 'F')),
> PRIMARY KEY (course_yr, test_nr, first_name));
>
> This is what you are trying to get as a result because of the failed
> DDL.
>
>
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: SQL Server Profiler Determine Number Times User Account Used
Next: Speed Up OPENROWSET?