Solved

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

Posted on 2011-03-15
4
507 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 500 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

679 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