SQL DISTINCT on Multiple Columns

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Sarvesh Singh » SQL DISTINCT on Multiple Columns

37 posts, Page 2 of 4

««1 2 3 4 »»»

SQL DISTINCT on Multiple Columns

Rate Topic

Display Mode

Topic Options

Author

Message

Jason-299789

Posted Tuesday, February 08, 2011 3:14 AM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 1:30 AM
Points: 803, Visits: 2,124

murat.korkmaz (2/8/2011)

To much failures, it's confusing, specially for the newbies!

Sorry, but it is a clearly written article and far from confusing. Yes there are a few errors, but they are minimal and easily made.

All in all its a good article and the writer should keep writing them.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1060099

Marco V

Posted Tuesday, February 08, 2011 5:59 AM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 02, 2013 7:10 AM
Points: 462, Visits: 501

i liked it, gave 3 stars, maybe a thing to consider is to explain what youre audience for this article is. It prevent of being judge on why you did not explain when to use distinct or group by in certain situations.

For a basic understanding of the use of distinct on mulitple colums i enjoyed reading it

Post #1060150

ss-457805

Posted Tuesday, February 08, 2011 6:45 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 2:56 AM
Points: 356, Visits: 1,659

Thank you all for your feedback. It means a lot.

The purpose of this artcle was to show simple usage of DISTINCT to a beginner.

blog: http://sarveshsingh.com

Twitter: @sarveshsing

Post #1060168

Thato

Posted Tuesday, February 08, 2011 6:52 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 22, 2013 1:25 AM
Points: 162, Visits: 128

Good article. Straight to the point. Good examples too. Needs to work a little bit more on presentation,spell check and sort order options for output readability and quick comparison. Another small bit of advice, make the habit of using common table expressions. Keep it up. Thank you.

Post #1060171

brichardson

Posted Tuesday, February 08, 2011 7:24 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, July 21, 2011 7:05 AM
Points: 2, Visits: 15

Good basic article on DISTINCT. Hadn't thought about using DISTINCT in aggregates--good idea.

Post #1060196

mtassin

Posted Tuesday, February 08, 2011 8:15 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 5:11 AM
Points: 3,229, Visits: 64,302

This was a great article on distinct.

Did I learn anything about Distinct? No.

But that doesn't mean that somebody else can't come along and learn something valuable from this article. Keep writing them. SSC has quickly eclipsed MSDN as a go to source to learn how to code SQL and it's the primary place I send developers to learn it.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems

Post #1060271

Amy.G

Posted Tuesday, February 08, 2011 8:17 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, May 10, 2013 9:35 AM
Points: 94, Visits: 258

This is the second article in a row that have had errors in them. I hate to be the person who uses the comments section to complain about typos, but in code it's pretty important. I like the articles, just a little more care in proofing would be great.

Amy

Post #1060277

Mister Sachmo

Posted Tuesday, February 08, 2011 9:05 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:31 PM
Points: 56, Visits: 128

as a (relative) beginner, it helped illustrate the concept for me much better than BOL does. it also inspired me to do some side by side runs on our data to see if there was any perf difference between distinct and group by.

i created two queries in different SPIDS. one was select DISTINCT column, one was SELECT column GROUP BY column.
The column that i chose was indexed.

The statistics io output was exactly the same for this simple query. I added a couple of more columns to the queries, and im still getting the same stats.

I'm going to try some more examples, but thanks for the inspiration to test this!

Post #1060336

pgoldy

Posted Tuesday, February 08, 2011 9:10 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 8:47 AM
Points: 3, Visits: 30

Perhaps I missed something, but one of the query samples doesn't behave as expected. The article says, "Let's look at another example where you can use DISTINCT on multiple columns to find duplicate addreses. I've taken this example from the post. Please refer to this post for more detail." Followed by this query:

SELECT PostCode, COUNT(PostCode)
FROM
(
SELECT DISTINCT Address1, Address2, City, PostCode
FROM DuplicateTest
) AS Sub
GROUP BY PostCode
HAVING COUNT(PostCode) > 1

However, this query does not return a duplicate address. Here's an example of not returning a duplicate address:

CREATE TABLE DuplicateTest(
Firstname nvarchar (30) NOT NULL,
Lastname nvarchar(30) NOT NULL,
Address1 nvarchar(50) NOT NULL,
Address2 nvarchar(50) NULL,
PostCode nvarchar(15) NOT NULL,
City nvarchar(30) NOT NULL
)

insert into DuplicateTest
(Firstname,Lastname, Address1, PostCode,City)
values
('Param', 'Singh', '12 Church Street', 'B283SP', 'Birmingham'),
('Steve', 'White', '1 May Lane', 'EC224HQ', 'London'),
('Mark', 'Smith', '42 May Lane', 'L324JK', 'Liverpool'),
('Claire', 'whitehood', '789 High Road', 'M236DM', 'Manchester'),
('P', 'Singh', '12 Church Street', 'B283SP', 'Birmingham')

SELECT PostCode, COUNT(PostCode)
FROM
(
SELECT DISTINCT Address1, Address2, City, PostCode
FROM DuplicateTest
) AS Sub
GROUP BY PostCode
HAVING COUNT(PostCode) > 1

You can see that records 1 and 5 have the same address for Adress1, PostCode and City. The firstnames are different - which is typical when identifying duplicate records in a mailing list. The query provided returns no records.

The queries included in an article should perform what the article states. Rated - 1 star.

P Goldy

Post #1060342

Jason-299789

Posted Tuesday, February 08, 2011 9:16 AM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 1:30 AM
Points: 803, Visits: 2,124

Goldy it wouldnt as you've already done a distinct Select on the Address which is the same, thus by the time you get to the outer query you only have 1 record returned to do the count on.

If you change the Second instance of the address to house number 14, you will get a count of two for that postal code.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1060351

« Prev Topic | Next Topic »

37 posts, Page 2 of 4

««1 2 3 4 »»»

Permissions