CODEWITHSUNDEEP
asp.nett-sqlstored-procedures
IrishChieftain
IrishChieftain

Reputation: 15253

Use of DISTINCT with T-SQL Stored Procedure

I'm running the following stored procedure and there's a join of Classes and Dates tables. However, out of seven test records, I'm getting a single duplicate record in the results:

SELECT DISTINCT dbo.Classes.Title, dbo.Classes.ClassTime, dbo.Classes.Category, 
    dbo.Classes.SubCategory1, dbo.Classes.[Description],dbo.Classes.ContactName, 
    dbo.Classes.ContactPhone, dbo.Classes.Location, dbo.Classes.Room,
    dbo.Dates.StartDate
    FROM dbo.Classes INNER JOIN dbo.Dates ON dbo.Classes.ClassID = dbo.Dates.ClassID
    ORDER BY StartDate DESC

Upvotes: 1

Views: 1136

Answers (1)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

Most likely one of your Date columns of those two rows differ somewhat but the difference doesn't show up in the output.

You can verify this by dropping those columns from your results.

SELECT DISTINCT dbo.Classes.Title
       , dbo.Classes.Category
       , dbo.Classes.SubCategory1
       , dbo.Classes.[Description]
       , dbo.Classes.ContactName
       , dbo.Classes.ContactPhone
       , dbo.Classes.Location
       , dbo.Classes.Room
FROM   dbo.Classes 
       INNER JOIN dbo.Dates ON dbo.Classes.ClassID = dbo.Dates.ClassID

On a different note, I would advice you to use aliases to improve the readability of the statement.

SELECT DISTINCT dbo.Classes.Title
       , c.Category
       , c.SubCategory1
       , c.[Description]
       , c.ContactName
       , c.ContactPhone
       , c.Location
       , c.Room
FROM   dbo.Classes AS c
       INNER JOIN dbo.Dates AS d ON c.ClassID = d.ClassID

Upvotes: 2

Related Questions