SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 row number
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

peace
Constraint Violating Yak Guru

389 Posts

Posted - 04/11/2013 :  23:42:20  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 04/12/2013 :  00:15:20  Show Profile  Reply with Quote
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

Edited by - bandi on 04/12/2013 00:17:33
Go to Top of Page

peace
Constraint Violating Yak Guru

389 Posts

Posted - 04/12/2013 :  00:22:06  Show Profile  Reply with Quote
it still give me the same result:

col1 col2 RN
aaa 123 1
bbb 123 2
ccc 456 1
Go to Top of Page

peace
Constraint Violating Yak Guru

389 Posts

Posted - 04/12/2013 :  00:25:58  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 04/12/2013 :  00:26:40  Show Profile  Reply with Quote
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
Go to Top of Page

peace
Constraint Violating Yak Guru

389 Posts

Posted - 04/12/2013 :  00:30:26  Show Profile  Reply with Quote
as above
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 04/12/2013 :  00:34:01  Show Profile  Reply with Quote
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

Edited by - bandi on 04/12/2013 01:13:19
Go to Top of Page

peace
Constraint Violating Yak Guru

389 Posts

Posted - 04/12/2013 :  00:38:29  Show Profile  Reply with Quote
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
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/12/2013 :  01:09:17  Show Profile  Visit russell's Homepage  Reply with Quote
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...

Edited by - russell on 04/12/2013 01:12:03
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000