Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

SQL DISTINCT on Multiple Columns Expand / Collapse
Author
Message
Posted Tuesday, February 8, 2011 3:14 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 9:25 AM
Points: 953, Visits: 2,626
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
Posted Tuesday, February 8, 2011 5:59 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, December 1, 2014 1:55 AM
Points: 462, Visits: 530
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
Posted Tuesday, February 8, 2011 6:45 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 7:49 AM
Points: 357, Visits: 1,720
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
Posted Tuesday, February 8, 2011 6:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 6:32 AM
Points: 164, Visits: 180
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
Posted Tuesday, February 8, 2011 7:24 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, February 8, 2011 8:15 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
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
Posted Tuesday, February 8, 2011 8:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, January 10, 2014 9:03 AM
Points: 103, Visits: 289
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
Posted Tuesday, February 8, 2011 9:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, November 24, 2014 3:43 PM
Points: 58, Visits: 143
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
Posted Tuesday, February 8, 2011 9:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 26, 2014 4:15 PM
Points: 3, Visits: 39
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
Posted Tuesday, February 8, 2011 9:16 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 9:25 AM
Points: 953, Visits: 2,626
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 »

Add to briefcase ««1234»»»

Permissions Expand / Collapse