[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Count Column in View

Don

3/29/2007 5:14:00 PM

How do I write a view that counts the occurance of certain items in it.

Sample Table
1,Smith, 03/01/07
2,Grace, 03/02/07
3,Henry, 03/03/07
4,Smith, 03/04/07
5,Smith, 03/05/07
6,Grace, 03/06/07

The view would return
1,Smith, 03/01/07,1
2,Grace, 03/02/07,1
3,Henry, 03/03/07,1
4,Smith, 03/04/07,2
5,Smith, 03/05/07,3
6,Grace, 03/06/07,2
4 Answers

Tibor Karaszi

3/29/2007 5:38:00 PM

0

Here's a 2005 version, using DDL ant the end:

SELECT
c1
,c2
,c3
,ROW_NUMBER() OVER(PARTITION BY c2 ORDER BY c2, c1) AS cnt
FROM x
ORDER BY 1

create table x(c1 int, c2 varchar(10), c3 datetime)

insert into x
SELECT 1,'Smith', '03/01/07'
UNION SELECT 2,'Grace', '03/02/07'
UNION SELECT 3,'Henry', '03/03/07'
UNION SELECT 4,'Smith', '03/04/07'
UNION SELECT 5,'Smith', '03/05/07'
UNION SELECT 6,'Grace', '03/06/07'

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://www.solidqualitylea...


"Don" <Don@discussions.microsoft.com> wrote in message
news:81BDA5ED-E1FE-4117-8870-20BF6269E2A5@microsoft.com...
> How do I write a view that counts the occurance of certain items in it.
>
> Sample Table
> 1,Smith, 03/01/07
> 2,Grace, 03/02/07
> 3,Henry, 03/03/07
> 4,Smith, 03/04/07
> 5,Smith, 03/05/07
> 6,Grace, 03/06/07
>
> The view would return
> 1,Smith, 03/01/07,1
> 2,Grace, 03/02/07,1
> 3,Henry, 03/03/07,1
> 4,Smith, 03/04/07,2
> 5,Smith, 03/05/07,3
> 6,Grace, 03/06/07,2
>


Don

3/29/2007 6:38:00 PM

0

Is there a 2000 version?

"Tibor Karaszi" wrote:

> Here's a 2005 version, using DDL ant the end:
>
> SELECT
> c1
> ,c2
> ,c3
> ,ROW_NUMBER() OVER(PARTITION BY c2 ORDER BY c2, c1) AS cnt
> FROM x
> ORDER BY 1
>
> create table x(c1 int, c2 varchar(10), c3 datetime)
>
> insert into x
> SELECT 1,'Smith', '03/01/07'
> UNION SELECT 2,'Grace', '03/02/07'
> UNION SELECT 3,'Henry', '03/03/07'
> UNION SELECT 4,'Smith', '03/04/07'
> UNION SELECT 5,'Smith', '03/05/07'
> UNION SELECT 6,'Grace', '03/06/07'
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/d...
> http://www.solidqualitylea...
>
>
> "Don" <Don@discussions.microsoft.com> wrote in message
> news:81BDA5ED-E1FE-4117-8870-20BF6269E2A5@microsoft.com...
> > How do I write a view that counts the occurance of certain items in it.
> >
> > Sample Table
> > 1,Smith, 03/01/07
> > 2,Grace, 03/02/07
> > 3,Henry, 03/03/07
> > 4,Smith, 03/04/07
> > 5,Smith, 03/05/07
> > 6,Grace, 03/06/07
> >
> > The view would return
> > 1,Smith, 03/01/07,1
> > 2,Grace, 03/02/07,1
> > 3,Henry, 03/03/07,1
> > 4,Smith, 03/04/07,2
> > 5,Smith, 03/05/07,3
> > 6,Grace, 03/06/07,2
> >
>
>
>

Aaron [SQL Server MVP]

3/29/2007 6:44:00 PM

0

Sure, but it gets pretty expensive with larger resultsets...




create table #x(c1 int, c2 varchar(10), c3 datetime);

insert into #x
SELECT 1,'Smith', '03/01/07'
UNION SELECT 2,'Grace', '03/02/07'
UNION SELECT 3,'Henry', '03/03/07'
UNION SELECT 4,'Smith', '03/04/07'
UNION SELECT 5,'Smith', '03/05/07'
UNION SELECT 6,'Grace', '03/06/07';

SELECT
x.c1,
x.c2,
x.c3,
_rank = (SELECT COUNT(*)+1 FROM #x WHERE c2 = x.c2
AND c1 < x.c1)
FROM #x x
ORDER BY c1;

DROP TABLE #x;



--
Aaron Bertrand
SQL Server MVP
http://www.sq...
http://www.aspfa...




"Don" <Don@discussions.microsoft.com> wrote in message
news:954CC790-3AD3-4D9E-B831-05C128F7C218@microsoft.com...
> Is there a 2000 version?

Anith Sen

3/29/2007 6:46:00 PM

0

For some options see: www.projectdmx.com/tsql/ranking.aspx

--
Anith