CODEWITHSUNDEEP
sqlsql-server
numberwang
numberwang

Reputation: 313

SQL: Don't show duplicates

I want to not show duplicate rows in output for the sql below and so I have 'distinct' in there, but this doesn't work. I get output like:

PermitNumber PermitName CreatedOn  
111          ABCD       1/2/2011  
111          ABCD       3/4/2012  
222          DFES       3/6/2000

and I want only one row with 111 but I get more than 1 row because 111 has more than one modification, but I don't care if it has 1 or 1000.

select  distinct (dbo.PermitNumber(mp.PermitId)), 
        dbo.PermitName(mp.PermitId),
    mod.[CreatedOn] as [CreatedOn]
from    tblPermit mp, dbo.[tblModification] mod
where mod.PermitId = mp.PermitId
order by 1

Using SQL Server

Upvotes: 2

Views: 51713

Answers (5)

Taryn
Taryn

Reputation: 247700

Distinct applies to all columns so you could use an aggregate function:

  select  mp.PermitNumber,
    mp.PermitName,
    max(mod.[CreatedOn])as [CreatedOn]
  from    tblPermit mp
  Inner join dbo.[tblModification] mod
      on mod.PermitId = mp.PermitId
  Group by mp.PermitNumber,
    mp.PermitName
  order by 1

Upvotes: 10

Elastep
Elastep

Reputation: 3408

Than you should decide how to deal with a creation date. For example to show maximum of these days. Distinct is applied to all fields in the select clause.

select  distinct (dbo.PermitNumber(mp.PermitId)), 
        dbo.PermitName(mp.PermitId)
from    tblPermit mp, dbo.[tblModification] mod
where mod.PermitId = mp.PermitId
order by 1

This will work but without created date

Upvotes: 2

John Woo
John Woo

Reputation: 263723

;WITH latestRecord
AS
(
    SELECT  PermitNumber, PermitName, CreatedOn,
            ROW_NUMBER() OVER (PARTITION BY PermitNumber, PermitName
                                ORDER BY CreatedOn DESC) rn
    FROM    tblPermit
)
SELECT PermitNumber, PermitName, CreatedOn
FROM    latestRecord
WHERE   rn = 1

Upvotes: 2

KM.
KM.

Reputation: 103587

based on the limited info in the question try:

select  distinct (dbo.PermitNumber(mp.PermitId)), 
        dbo.PermitName(mp.PermitId),
    MIN(mod.[CreatedOn]) as [CreatedOn]
  --^^^new
from    tblPermit mp, dbo.[tblModification] mod
where mod.PermitId = mp.PermitId
group by (dbo.PermitNumber(mp.PermitId)), ---<<new
        dbo.PermitName(mp.PermitId),      ---<<new
order by 1

you need to do something with all the mod.[CreatedOn]) values for each row, so group them together and display the MIN of each.

Upvotes: 1

MicSim
MicSim

Reputation: 26796

Use GROUP BY (exact syntax depending on your dbms):

select  (dbo.PermitNumber(mp.PermitId)), 
    dbo.PermitName(mp.PermitId),
min(mod.[CreatedOn] as [CreatedOn])
from    tblPermit mp, dbo.[tblModification] mod
where mod.PermitId = mp.PermitId
group by (dbo.PermitNumber(mp.PermitId)), dbo.PermitName(mp.PermitId)
order by 1

Upvotes: 2

Related Questions