USING ROW_NUMBER(), STILL GETTING PROBLEM

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

USING ROW_NUMBER(), STILL GETTING PROBLEM

Author

Topic

sqldev6363
Yak Posting Veteran

54 Posts

Posted - 2010-12-21 : 08:48:44

I am using row_number() for the following scenario but i need to put them in different way..

table1

ID prov

1 A
1 B
2 A
2 A
2 B
3 B
3 B
3 A
3 B

TABLE2

ID PART PRICE
1 Y 123.0
1 N 0.0
2 Y 31.0
2 N 0.0
3 N 0.0
.
.
.

I NEED THE RESULT LIKE THIS

TABLE3
ID prov PRICE

1 A 123.0
1 B 0.0
2 A 31.0
2 A 0.0
2 B 0.0
3 B 0.0
3 B 0.0
3 A 0.0
3 B 0.0

IN TABLE2 FIELD 'Y' IN THE COLUMN PART WILL BE HAVING THE VALUE GREATER THAN 1 AND FOR THE FIELD 'N' WILL BE ALWAYS ZERO

I AM USING ROW_NUMBER() AND USING LEFT JOIN TO JOIN 2 TABLES HERE ,BUT IN THE RESULT TABLE3 I AM GETTING ALL 0.0 IN THE PRICE COLUMN.PROBLEM IS THAT ONE

CAN ANY ONE HELP ME OUT IFOR THIS SCENARIO...THANKS

dev

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-21 : 10:15:39

Use DENSE_RANK instead of ROW_NUMBER.
And there is no need to shout.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources