Using Partition By Clause - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Using Partition By Clause

Author

Topic

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-01-02 : 02:39:59

Hi all

I am new for this Forum. Kindly tell about partition by clause in SQL SERVER 2005,For Example

name name_id
X 2303
X 2303
y 2303
X 2456

I need output like these
name Items name_id
X 1 2303
X 2 2456
Y 1 2303

I want number of differenet items present in the name_id column

Kindly reply

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-02 : 02:52:55

[code]DECLARE @sample TABLE
(
[name] varchar(10),
name_id int
)
INSERT INTO @sample
SELECT 'X', 2303 UNION ALL
SELECT 'X', 2303 UNION ALL
SELECT 'Y', 2303 UNION ALL
SELECT 'X', 2456

SELECT [name],
Items = row_number() OVER (PARTITION BY [name] ORDER BY [name], name_id),
name_id
FROM @sample
GROUP BY [name], name_id
ORDER BY [name], name_id

/*
name Items name_id
---------- -------------------- -----------
X 1 2303
X 2 2456
Y 1 2303

(3 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-01-02 : 03:06:45

Thanks khtan

I am trying using partition clause however i get some errors

row_number is not a recognized name
I am using SQL SERVER 2000

Is any other way to write the same query

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-02 : 03:14:34

you posted in a SQL 2005 forum, asking for partition by clause in SQL 2005 and you are using SQL Server 2000 ?

KH
[spoiler]Time is always against us[/spoiler]

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-01-02 : 03:32:11

Actually my local system using 2000 but our server system using 2005

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-02 : 03:36:41

The query i posted is for 2005. row_number() and over(partition by . . .) is only available on 2005 onwards

KH
[spoiler]Time is always against us[/spoiler]

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-01-02 : 03:40:16

Any way Thanks for your reply

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-02 : 03:43:22

You can also use this method ( works for both 2000 & 2005)


DECLARE @sample TABLE
(
	[name]		varchar(10),
	name_id		int
)
INSERT INTO @sample
SELECT	'X', 2303 UNION ALL
SELECT	'X', 2303 UNION ALL
SELECT	'Y', 2303 UNION ALL
SELECT	'X', 2756 UNION ALL
SELECT	'X', 2303 UNION ALL
SELECT	'X', 2757 UNION ALL
SELECT	'Y', 2312 UNION ALL
SELECT	'Y', 2303 UNION ALL
SELECT	'Y', 2312 UNION ALL
SELECT	'Y', 2315 


SELECT [name],
       name_id,
       (SELECT COUNT(DISTINCT name_id) + 1 FROM @sample WHERE [name]=t1.[name]AND name_id < t1.name_id) AS 'Items'
FROM @sample t1
GROUP BY [name],name_id
ORDER BY [name],name_id

---- output---

name name_id Items
---------- ----------- -----------
X 2303 1
X 2756 2
X 2757 3
Y 2303 1
Y 2312 2
Y 2315 3
(6 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources