row number - 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 number

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-04-11 : 23:42:20

SELECT col1,col2,
ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col1,col2 desc) RN
from tableA order by col2

it show as below:

col1 col2 RN
aaa 123 1
bbb 123 2
ccc 456 1

the result show be:

col1 col2 RN
aaa 123 1
bbb 123 1
ccc 456 1

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-12 : 00:15:20

SELECT col1,col2,
ROW_NUMBER() OVER(PARTITION BY col2 ORDER BY col1 ) RN
from TableA order by col2

EDIT: If there is duplicates in col1 within each col2 group, then you can use the following
SELECT col1,col2,
ROW_NUMBER() OVER(PARTITION BY col2 ORDER BY col1 , col2 desc ) RN
from TableA order by col2

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-04-12 : 00:22:06

it still give me the same result:

col1 col2 RN
aaa 123 1
bbb 123 2
ccc 456 1

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-04-12 : 00:25:58

SELECT col1,col2,
ROW_NUMBER() OVER(PARTITION BY col2 ORDER BY col1 , col2 desc ) RN
from TableA where col1 in (aaa,bbb)

col1 col2 RN
aaa 1
aaa 123 1
bbb 123 2
bbb 456 1
bbb 2

Result should be as below:

col1 col2 RN
aaa 2
aaa 123 1
bbb 123 1
bbb 456 1
bbb 2

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-12 : 00:26:40

Post your atcual data....
--Illustration with your sample data working fine

DECLARE @tab TABLE(col1 char(3), col2 int)
insert into @tab
SELECT 'aaa', 123 union all
SELECT 'bbb', 123 union all
SELECT 'ccc', 456

SELECT * FROM @tab 

SELECT col1,col2,
ROW_NUMBER() OVER(PARTITION BY col2 ORDER BY col1 , col2 desc ) RN
from @tab order by col2

col1        col2
---- -----------
aaa          123
bbb          123
ccc          456


col1        col2                   RN
---- ----------- --------------------
aaa          123                    1
bbb          123                    2
ccc          456                    1

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-04-12 : 00:30:26

as above

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-12 : 00:34:01

Oh.. for this result, your code is working fine...

Result should be as below:
col1 col2 RN
aaa 2
aaa 123 1
bbb 123 1
bbb 456 1
bbb 2

Tell me the logic behind the result which you want............

EDIT: refere this link for ROW_NUMBER() OVER( PARTITION BY ORDER BY )
http://www.codeproject.com/Articles/308281/How-to-Use-ROW_NUMBER-to-Enumerate-and-Partition-R

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-04-12 : 00:38:29

my result didnt turn out as below:

col1 col2 RN
aaa 2
aaa 123 1
bbb 123 1
bbb 456 1
bbb 2

i would like to take only rn=1

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-12 : 01:09:17

this? if not, as bandi said, post actual DDL and sample data.


WITH cte
AS (
	SELECT 	col1,col2,
		ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col1,col2 desc) RN
	from 	tableA
)
SELECT	col1, col2, RN
FROM    cte
WHERE	RN = 1
order by
	col2

And, if you don't mind my saying so Peace, you've been around here long enough to know how to post a question that makes it possible to get good help AND how to format your code...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources