Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

how to get distinct row when use together with ROW_NUMBER()?

Posted on 2011-03-15
4
Medium Priority
?
517 Views
Last Modified: 2012-08-14
I try to get a list of distinct name from the query below:

Select distinct [rodnee_WISDAssetsInvRemedy].[CI_NAME], ROW_NUMBER() OVER ( Order By [rodnee_WISDAssetsInvRemedy].[CI_NAME] Asc  ) as 'RowNum'
FROM rodnee_WISDAssetsInvRemedy

but it only gave me duplicated names.
104LMWC                      1
104LMWC                      2
ADMHXP01      3
ADMHXP01      4
AIBSFIBCPAPP1      5
AIBSFIBCPAPP1      6
AIBSFIPRODAPP1      7
AIBSFIPRODAPP1      8
AIBSFIUATAPP1      9
AIBSFIUATAPP1      10

Very appreciated for your help!!!
0
Comment
Question by:jssong2000
4 Comments
 
LVL 5

Expert Comment

by:KGNickl
ID: 35141102
I believe its returning unique combinations of both name and row number.
0
 
LVL 9

Expert Comment

by:joshbula
ID: 35141237
SELECT (Select distinct [rodnee_WISDAssetsInvRemedy].[CI_NAME],
FROM rodnee_WISDAssetsInvRemedy ) As CI_NAME, Row_Number(), OVER ( Order By [rodnee_WISDAssetsInvRemedy].[CI_NAME] Asc  ) as 'RowNum',
0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 2000 total points
ID: 35141297
why not simply

Select [rodnee_WISDAssetsInvRemedy].[CI_NAME], ROW_NUMBER() OVER ( Order By [rodnee_WISDAssetsInvRemedy].[CI_NAME] Asc  ) as 'RowNum'
FROM rodnee_WISDAssetsInvRemedy
group by [rodnee_WISDAssetsInvRemedy].[CI_NAME]
0
 

Author Closing Comment

by:jssong2000
ID: 35141590
Thank you very much!!!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question