Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL DISTINCT on Multiple Columns


SQL DISTINCT on Multiple Columns

Author
Message
ss-457805
ss-457805
Old Hand
Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)Old Hand (379 reputation)

Group: General Forum Members
Points: 379 Visits: 1878
Comments posted to this topic are about the item SQL DISTINCT on Multiple Columns

blog: http://sarveshsingh.com

Twitter: @sarveshsing
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45113 Visits: 39916
Wow... tough crowd. A bit cowardly too. People apparently have, so far, rated this article in the 1 or 2 star range but no one had the nads to say why.

@Sarvesh,

This is a good introductory article on DISTINCT and will help many a newbie. Although others have, apparently, not recognized it as such, this is a fine bit of "SQL Spackle" with some very easy to understand examples with output results and explanations as well as the GROUP BY alternatives. Don't let the folks that don't recognize what this article is for discourage you. Keep writing them.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
nwilske
nwilske
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 21
The input data does not match the examples later in the article. Param Singh is added twice, and this was not the intention.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16453 Visits: 13207
Although the article was well-written and a good introduction to DISTINCT, I didn't give it many stars.
Why? I found that the article didn't introduce us anything that can't be found on the MSDN pages.

It would've been nice if the following were included in the article:

* a critical look on the performance impact of DISTINCT
* although the alternatives of GROUP BY are listed, it is not explained if one construct is prefered above the other, or if they are equivalent (if you look at the execution plans)
* the behaviour of DISTINCT with NULL values and in aggregate functions

However, the article itself was written well, with no obvious spelling mistakes and it had clear examples, so Sarvesh, as Jeff already mentioned, don't let this withhold you to write future articles!



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
srivatsa.hg
srivatsa.hg
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 8
The INSERT had these data :

('Param', 'Singh', 'B283SP', 'Birmingham'),
('Steve', 'White', 'EC224HQ', 'London'),
('Mark', 'Smith', 'L324JK', 'Liverpool'),
('Claire', 'whitehood', 'M236DM', 'Manchester'),
('Param', 'Singh', 'B283SP', 'Birmingham')


And the Select * from the table yielded

Sarvesh Singh B263SP Birmingham
Steve White EC224HQ London
Mark Smith L324JK Liverpool
Claire whitehood M236DM Manchester
Param Singh B283SP Birmingham

I presume this is a typo.But you need to edit this asap.
majorbloodnock
majorbloodnock
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1113 Visits: 3062
Koen (da-zero) (2/8/2011)
Although the article was well-written and a good introduction to DISTINCT, I didn't give it many stars.
Why? I found that the article didn't introduce us anything that can't be found on the MSDN pages.

I see it from a slightly different perspective; there are very few articles, here or anywhere, that give any information above what's available in MSDN. Their value is in their ability to explain concepts better than MSDN and to serve up information in bite-sized chunks. Both these approaches make the same information far more readily accessible, and I believe this article is another successful example.


It would've been nice if the following were included in the article:

* a critical look on the performance impact of DISTINCT
* although the alternatives of GROUP BY are listed, it is not explained if one construct is prefered above the other, or if they are equivalent (if you look at the execution plans)
* the behaviour of DISTINCT with NULL values and in aggregate functions

However, the article itself was written well, with no obvious spelling mistakes and it had clear examples, so Sarvesh, as Jeff already mentioned, don't let this withhold you to write future articles!

Here I agree. I'm not entirely sure wandering into execution plans would have been entirely appropriate for the intended audience, but a mention of performance, alternative functions and working with NULLs could have been included to good effect.

Overall, I think it was a good beginner's introduction to an important SQL concept.

Semper in excretia, sumus solum profundum variat
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
A good introduction to Distinct for novices, though it it really doesnt explain why you would consider using a Group by to get a distinct list, is there a performance differential to consider when using one method or the other?

I also consider that these two SQL scripts are completely different and thus shouldnt be considered alternatives for each other

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

and

SELECT Address1,Address2,City,PostCode,Count(PostCode)
FROM AddressTable
GROUP BY Address1,Address2,City,PostCode
HAVING Count(PostCode) > 1

The first one would work on the following list of data

B23 3SP, 1
EC1A 3AD, 2.

which would result in only the EC1A 3AD, 2 row being returned, however the alternative query works on a totally different set of data, whch may be :

6 some road, some district, B23 6QD, 1
8 another road, another district, EC1A 3AD, 1
10 another road, another district, EC1A 3AD, 1

So with the Having Count()>1 clause you would get no results returned.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
James A Skipwith
James A Skipwith
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 640
I would agree that this is a good introductory article on DISTINCT and will help many a newbie. A minor section on performance may have been beneficial but it is not critical, as the article is aimed at novice users and getting them to start writing SQL. Tuning is a whole other topic and all they need to know is maybe to use distinct with caution and always try with group by as well to see which is faster (as this is the best indicator for a newbie who wouldn't understand an execution plan as yet).

Do keep on writing Sarvesh. I did my my first one yesterday and actually as a result learnt some things that I didn't know - which is always a bonus - and got some quality feedback from some very wise and kind folks. If you're brave enough to put your articles out you have to expect some pot shots but it's all a learning curve, so don't let it get you down. I'll be expecting an in-depth article on the performance differences between DISTINCT and GROUP BY from you soon!

James

James
MCM [@TheSQLPimp]
murat.korkmaz
murat.korkmaz
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 49
To much failures, it's confusing, specially for the newbies!
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16453 Visits: 13207
murat.korkmaz (2/8/2011)
To much failures, it's confusing, specially for the newbies!


Can you elaborate on that? What were the failures (aside from the typo in the input query) and what parts were confusing?
Help the author to improve his article.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search