sanjay prajapati sanjay prajapati -4 years ago 120
SQL Question

DISTINCT with ROW_NUMBER()

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

Answer Source

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 ''

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download