computing a counter/counting column?
in [Programming SQL]
|
From: Rich on 1 Dec 2009 11:27 The following table (query) lists individuals once, twice, three times, or more. I need to compute/derive the count (the progressive count - incremented by one for each person) of each entry for each person -- the count to be displayed in the computed/derived column which I will call "Attempt". Fred is listed once. The query should list a 1 in the Attempt column. Jane is listed 3 times. The query should list Jane's first entry wtih 1 in the Attempt column, the 2nd entry for Jane will have a 2, the 3rd entry for Jane will have a 3. Bill is listed twice. The query should list Bill's first entry with a 1 in the attemp column. The 2nd entry for Bill will have a 2 in the attempt column. What would be the tSql to perform this kind of query? create table #tmp1(rowID int Identity(1,1), yr int, fName varchar(50), grade varchar(1)) --raw data insert into #tmp1 (yr, fName, grade) select 2007, 'Fred', 'P' union all select 2007, 'Jane', 'F' union all select 2008, 'Jane', 'F' union all select 2009, 'Jane', 'P' union all select 2007, 'Bill', 'F' union all select 2008, 'Bill', 'F' select * from #tmp1 returns the following data set 1 2007 Fred P 2 2007 Jane F 3 2008 Jane F 4 2009 Jane P 5 2007 Bill F 6 2008 Bill F I need the query to return a computed/derived column that I call Attempt: 1 2007 Fred P 1 2 2007 Jane F 1 3 2008 Jane F 2 4 2009 Jane P 3 5 2007 Bill F 1 6 2008 Bill P 2 Thanks, Rich
From: Tom Cooper on 1 Dec 2009 11:37 With cte As (Select rowID, yr, fName, grade, Row_Number() Over (Partition By fName Order By yr) As Attempt From #tmp1) Select rowID, yr, fName, grade, Attempt From cte Order By rowID; Tom "Rich" <Rich(a)discussions.microsoft.com> wrote in message news:05AC588E-38BD-4D31-B5EE-92820ADD04D0(a)microsoft.com... > The following table (query) lists individuals once, twice, three times, or > more. I need to compute/derive the count (the progressive count - > incremented by one for each person) of each entry for each person -- the > count to be displayed in the computed/derived column which I will call > "Attempt". Fred is listed once. The query should list a 1 in the Attempt > column. Jane is listed 3 times. The query should list Jane's first entry > wtih 1 in the Attempt column, the 2nd entry for Jane will have a 2, the > 3rd > entry for Jane will have a 3. Bill is listed twice. The query should > list > Bill's first entry with a 1 in the attemp column. The 2nd entry for Bill > will have a 2 in the attempt column. What would be the tSql to perform > this > kind of query? > > create table #tmp1(rowID int Identity(1,1), yr int, fName varchar(50), > grade > varchar(1)) > > --raw data > insert into #tmp1 (yr, fName, grade) > select 2007, 'Fred', 'P' > union all > select 2007, 'Jane', 'F' > union all > select 2008, 'Jane', 'F' > union all > select 2009, 'Jane', 'P' > union all > select 2007, 'Bill', 'F' > union all > select 2008, 'Bill', 'F' > > select * from #tmp1 > > returns the following data set > > 1 2007 Fred P > 2 2007 Jane F > 3 2008 Jane F > 4 2009 Jane P > 5 2007 Bill F > 6 2008 Bill F > > I need the query to return a computed/derived column that I call Attempt: > > 1 2007 Fred P 1 > 2 2007 Jane F 1 > 3 2008 Jane F 2 > 4 2009 Jane P 3 > 5 2007 Bill F 1 > 6 2008 Bill P 2 > > Thanks, > Rich
From: Rich on 1 Dec 2009 12:28 Nice! Works perfectly. I confess that I am not up on Sql2005 (or higher) tsql enhancements. In an effort to understand/appreciate what is happening here - what would be the workaround version of this for Sql2000 tSql? "Tom Cooper" wrote: > With cte As > (Select rowID, yr, fName, grade, > Row_Number() Over (Partition By fName Order By yr) As Attempt > From #tmp1) > Select rowID, yr, fName, grade, Attempt > From cte > Order By rowID; > > Tom > > "Rich" <Rich(a)discussions.microsoft.com> wrote in message > news:05AC588E-38BD-4D31-B5EE-92820ADD04D0(a)microsoft.com... > > The following table (query) lists individuals once, twice, three times, or > > more. I need to compute/derive the count (the progressive count - > > incremented by one for each person) of each entry for each person -- the > > count to be displayed in the computed/derived column which I will call > > "Attempt". Fred is listed once. The query should list a 1 in the Attempt > > column. Jane is listed 3 times. The query should list Jane's first entry > > wtih 1 in the Attempt column, the 2nd entry for Jane will have a 2, the > > 3rd > > entry for Jane will have a 3. Bill is listed twice. The query should > > list > > Bill's first entry with a 1 in the attemp column. The 2nd entry for Bill > > will have a 2 in the attempt column. What would be the tSql to perform > > this > > kind of query? > > > > create table #tmp1(rowID int Identity(1,1), yr int, fName varchar(50), > > grade > > varchar(1)) > > > > --raw data > > insert into #tmp1 (yr, fName, grade) > > select 2007, 'Fred', 'P' > > union all > > select 2007, 'Jane', 'F' > > union all > > select 2008, 'Jane', 'F' > > union all > > select 2009, 'Jane', 'P' > > union all > > select 2007, 'Bill', 'F' > > union all > > select 2008, 'Bill', 'F' > > > > select * from #tmp1 > > > > returns the following data set > > > > 1 2007 Fred P > > 2 2007 Jane F > > 3 2008 Jane F > > 4 2009 Jane P > > 5 2007 Bill F > > 6 2008 Bill F > > > > I need the query to return a computed/derived column that I call Attempt: > > > > 1 2007 Fred P 1 > > 2 2007 Jane F 1 > > 3 2008 Jane F 2 > > 4 2009 Jane P 3 > > 5 2007 Bill F 1 > > 6 2008 Bill P 2 > > > > Thanks, > > Rich > > . >
From: Rich on 1 Dec 2009 13:01 Nevermind. I kind of figured this out. Thanks again. "Rich" wrote: > Nice! Works perfectly. I confess that I am not up on Sql2005 (or higher) > tsql enhancements. > > In an effort to understand/appreciate what is happening here - what would be > the workaround version of this for Sql2000 tSql? > > > > "Tom Cooper" wrote: > > > With cte As > > (Select rowID, yr, fName, grade, > > Row_Number() Over (Partition By fName Order By yr) As Attempt > > From #tmp1) > > Select rowID, yr, fName, grade, Attempt > > From cte > > Order By rowID; > > > > Tom > > > > "Rich" <Rich(a)discussions.microsoft.com> wrote in message > > news:05AC588E-38BD-4D31-B5EE-92820ADD04D0(a)microsoft.com... > > > The following table (query) lists individuals once, twice, three times, or > > > more. I need to compute/derive the count (the progressive count - > > > incremented by one for each person) of each entry for each person -- the > > > count to be displayed in the computed/derived column which I will call > > > "Attempt". Fred is listed once. The query should list a 1 in the Attempt > > > column. Jane is listed 3 times. The query should list Jane's first entry > > > wtih 1 in the Attempt column, the 2nd entry for Jane will have a 2, the > > > 3rd > > > entry for Jane will have a 3. Bill is listed twice. The query should > > > list > > > Bill's first entry with a 1 in the attemp column. The 2nd entry for Bill > > > will have a 2 in the attempt column. What would be the tSql to perform > > > this > > > kind of query? > > > > > > create table #tmp1(rowID int Identity(1,1), yr int, fName varchar(50), > > > grade > > > varchar(1)) > > > > > > --raw data > > > insert into #tmp1 (yr, fName, grade) > > > select 2007, 'Fred', 'P' > > > union all > > > select 2007, 'Jane', 'F' > > > union all > > > select 2008, 'Jane', 'F' > > > union all > > > select 2009, 'Jane', 'P' > > > union all > > > select 2007, 'Bill', 'F' > > > union all > > > select 2008, 'Bill', 'F' > > > > > > select * from #tmp1 > > > > > > returns the following data set > > > > > > 1 2007 Fred P > > > 2 2007 Jane F > > > 3 2008 Jane F > > > 4 2009 Jane P > > > 5 2007 Bill F > > > 6 2008 Bill F > > > > > > I need the query to return a computed/derived column that I call Attempt: > > > > > > 1 2007 Fred P 1 > > > 2 2007 Jane F 1 > > > 3 2008 Jane F 2 > > > 4 2009 Jane P 3 > > > 5 2007 Bill F 1 > > > 6 2008 Bill P 2 > > > > > > Thanks, > > > Rich > > > > . > >
From: --CELKO-- on 1 Dec 2009 14:38 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.
|
Next
|
Last
Pages: 1 2 Prev: SQL Server Profiler Determine Number Times User Account Used Next: Speed Up OPENROWSET? |