DISTINCT with ROW_NUMBER()

Question

Need to add Distinct with Row_Number in given query, I have used this query for gridview in asp.net:-

SELECT DISTINCT ROW_NUMBER() OVER (order by tbpan) AS 'Sr. No',
        case when tbprofile =  '3' then  'Applicant TBI'
             when tbprofile = '4' then  'Prayas Center'
             end 'Applicant Type',
        REPLACE(ISNULL(DATEPART(yyyy,b.govtimevalid), '-'),0,'-') as 'Year',
        [tbpan] AS 'Applicant Id',
        ISNULL(a.PCId, '-') as 'PCId',
        UPPER(tbname) AS 'Name',
        UPPER(isnull(formstatus,'IN PROGRESS')) AS 'Form Status',UPPER(isnull(pmuapproval,'-')) AS 'PMU Status', 
        case when pmuapproval = 'valid'
        then isnull (convert(Varchar, pmutimevalid, 107),'-')
        else isnull (convert(Varchar, pmutimeinvalid, 107),'-')
        end 'PMUDateTime',
        UPPER(ISNULL(govapproval,'-')) AS 'PMC Status',
        case when govapproval = 'valid'
        then isnull (convert(Varchar, govtimevalid, 107),'-')
        else isnull( convert(Varchar, govtimeinvalid, 107),'-')
        end 'PMCDateTime',
        ISNULL(SanctionedAmount,'0') AS 'Sanctioned Amount',
        ISNULL((SanctionedAmount-BalDisbursed),'0') AS 'Total Disbursed',ISNULL(BalDisbursed,'0') AS 'Total Balance' 
        FROM tb_User a 
        LEFT OUTER JOIN applied b ON a.tbpan=b.tbid 
        LEFT OUTER JOIN tb_SanctionInfo c ON a.PCId = c.PCId 
        LEFT OUTER JOIN tb_DisbursedInfo d ON c.PCId = d.PCId WHERE tbprofile !='1' AND tbprofile !='2'

Added image for reference

Sample data and desired results would help explain what you want to do. In addition, your question suggests that you could ask about a much simpler query. — Gordon Linoff, Feb 28 '17 at 11:50

score 3 · Answer 1 · answered Feb 28 '17 at 11:51

Try it like this:

SELECT ROW_NUMBER() OVER(ORDER BY SomeColumn) AS RowNr
      ,tbl.*
FROM
(SELECT DISTINCT ....) AS tbl

ROW_NUMBER() will generate a number for each row. DISTINCT is looking for identical rows. But - as they have a running number each, they are not identical...

score 0 · Accepted Answer · answered Feb 28 '17 at 11:52

Slap it in a CTE first:

with CTE as
(
SELECT DISTINCT 
        case when tbprofile =  '3' then  'Applicant TBI'
             when tbprofile = '4' then  'Prayas Center'
             end 'Applicant Type',
        REPLACE(ISNULL(DATEPART(yyyy,b.govtimevalid), '-'),0,'-') as 'Year',
        [tbpan] AS 'Applicant Id',
        ISNULL(a.PCId, '-') as 'PCId',
        UPPER(tbname) AS 'Name',
        UPPER(isnull(formstatus,'IN PROGRESS')) AS 'Form Status',UPPER(isnull(pmuapproval,'-')) AS 'PMU Status', 
        case when pmuapproval = 'valid'
        then isnull (convert(Varchar, pmutimevalid, 107),'-')
        else isnull (convert(Varchar, pmutimeinvalid, 107),'-')
        end 'PMUDateTime',
        UPPER(ISNULL(govapproval,'-')) AS 'PMC Status',
        case when govapproval = 'valid'
        then isnull (convert(Varchar, govtimevalid, 107),'-')
        else isnull( convert(Varchar, govtimeinvalid, 107),'-')
        end 'PMCDateTime',
        ISNULL(SanctionedAmount,'0') AS 'Sanctioned Amount',
        ISNULL((SanctionedAmount-BalDisbursed),'0') AS 'Total Disbursed',ISNULL(BalDisbursed,'0') AS 'Total Balance' 
        FROM tb_User a 
        LEFT OUTER JOIN applied b ON a.tbpan=b.tbid 
        LEFT OUTER JOIN tb_SanctionInfo c ON a.PCId = c.PCId 
        LEFT OUTER JOIN tb_DisbursedInfo d ON c.PCId = d.PCId WHERE tbprofile !='1' AND tbprofile !='2' 
)
select CTE.*, ROW_NUMBER() OVER (order by [Applicant ID]) AS 'Sr. No'
from CTE

Also, For SQL server, use [] for alias, not ''

SQL Server also supports double quotes for delimited identifiers. Why not chose the ANSI SQL way, and write (more) portable code? — jarlh, Feb 28 '17 at 11:58
"Slap it in a CTE first"...It is still returning duplicate values. — sanjay prajapati, Feb 28 '17 at 12:08
@sanjayprajapati Then there must be differences between each row in the `select distinct...` — JohnHC, Feb 28 '17 at 13:19

score 0 · Answer 3 · answered Feb 28 '17 at 11:54

   SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS  'Sr. No', 'Applicant Type','Applicant Id','PCId','Name', 'PMU Status','PMUDateTime','PMC Status',
   'PMCDateTime','Sanctioned Amount','Total Balance'
   FROM  (
      DISTINCT 
        case when tbprofile =  '3' then  'Applicant TBI'
             when tbprofile = '4' then  'Prayas Center'
             end 'Applicant Type',
        REPLACE(ISNULL(DATEPART(yyyy,b.govtimevalid), '-'),0,'-') as 'Year',
        [tbpan] AS 'Applicant Id',
        ISNULL(a.PCId, '-') as 'PCId',
        UPPER(tbname) AS 'Name',
        UPPER(isnull(formstatus,'IN PROGRESS')) AS 'Form Status',UPPER(isnull(pmuapproval,'-')) AS 'PMU Status', 
        case when pmuapproval = 'valid'
        then isnull (convert(Varchar, pmutimevalid, 107),'-')
        else isnull (convert(Varchar, pmutimeinvalid, 107),'-')
        end 'PMUDateTime',
        UPPER(ISNULL(govapproval,'-')) AS 'PMC Status',
        case when govapproval = 'valid'
        then isnull (convert(Varchar, govtimevalid, 107),'-')
        else isnull( convert(Varchar, govtimeinvalid, 107),'-')
        end 'PMCDateTime',
        ISNULL(SanctionedAmount,'0') AS 'Sanctioned Amount',
        ISNULL((SanctionedAmount-BalDisbursed),'0') AS 'Total Disbursed',ISNULL(BalDisbursed,'0') AS 'Total Balance' 
        FROM tb_User a 
        LEFT OUTER JOIN applied b ON a.tbpan=b.tbid 
        LEFT OUTER JOIN tb_SanctionInfo c ON a.PCId = c.PCId 
        LEFT OUTER JOIN tb_DisbursedInfo d ON c.PCId = d.PCId WHERE tbprofile !='1' AND tbprofile !='2' 
  ) T

ROW_NUMBER() OVER(ORDER BY (SELECT 1)) For Sr No Like 1,2,3.... — Singh Kailash, Feb 28 '17 at 11:59

DISTINCT with ROW_NUMBER()

3 Answers3