Row Count - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Row Count

Author

Topic

asif372
Posting Yak Master

100 Posts

Posted - 2011-10-07 : 08:23:52

Dear i want To Count Row
I am Doing Like This

select
t1.ItemNo,t1.DocDate,
(SELECT TOP 1 COUNT(*)
FROM SAP t
WHERE t.itemNo = t1.itemNo AND t.DocDate = t1.DocDate ) AS rn
from SAP t1
ORDER by DocDate,ItemNo,rn

but Receiving data like this

(Item no) (Date) (Row No)
SF00037 2011-09-09 00:00:00 4
SF00037 2011-09-09 00:00:00 4
SF00037 2011-09-09 00:00:00 4
SF00037 2011-09-09 00:00:00 4

I want Data Like this

(Item no) (Date) (Row No)
SF00037 2011-09-09 00:00:00 0
SF00037 2011-09-09 00:00:00 1
SF00037 2011-09-09 00:00:00 2
SF00037 2011-09-09 00:00:00 3

Any Ideas
Thanks in Advance

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-10-07 : 08:30:40

A little confused about your requirement but is this what you want? ->

SELECT *
FROM (
	select 
		t1.ItemNo,t1.DocDate,
		RowNum = ROW_NUMBER() OVER (ORDER BY DocDate,ItemNo) - 1
	from SAP t1) a
ORDER by DocDate,ItemNo,RowNum

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

asif372
Posting Yak Master

100 Posts

Posted - 2011-10-07 : 08:46:37

no i want to count Itemno where order by Date and Itemno
Just like this

(Item no) (Date) (Row No)
SF00037 2011-09-09 00:00:00 0
SF00037 2011-09-09 00:00:00 1
SF00037 2011-09-09 00:00:00 2
SF00037 2011-09-09 00:00:00 3

SF00038 2011-09-09 00:00:00 0
SF00038 2011-09-09 00:00:00 1

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-10-07 : 09:01:54

Ok, here goes:


SELECT *
FROM (
	select 
		t1.ItemNo,t1.DocDate,
		RowNum = ROW_NUMBER() OVER (PARTITION BY DocDate,ItemNo ORDER BY DocDate,ItemNo) - 1
	from SAP t1) a
ORDER by DocDate,ItemNo,RowNum

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources