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 Monday, February 07, 2011 10:10 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 07, 2013 2:07 PM
Points: 357, Visits: 1,674
Comments posted to this topic are about the item SQL DISTINCT on Multiple Columns

blog: http://sarveshsingh.com

Twitter: @sarveshsing
Post #1060020
Posted Tuesday, February 08, 2011 12:27 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:45 PM
Points: 36,013, Visits: 30,300
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1060051
Posted Tuesday, February 08, 2011 12:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 06, 2014 3:11 AM
Points: 3, Visits: 14
The input data does not match the examples later in the article. Param Singh is added twice, and this was not the intention.
Post #1060052
Posted Tuesday, February 08, 2011 1:24 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:08 AM
Points: 12,212, Visits: 9,193
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1060068
Posted Tuesday, February 08, 2011 1:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 08, 2011 8:24 AM
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.

Post #1060069
Posted Tuesday, February 08, 2011 2:03 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, January 13, 2014 4:35 AM
Points: 1,046, Visits: 2,997
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
Post #1060076
Posted Tuesday, February 08, 2011 2:05 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: Thursday, April 17, 2014 7:37 AM
Points: 860, Visits: 2,323
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
Post #1060077
Posted Tuesday, February 08, 2011 2:31 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 04, 2014 7:39 AM
Points: 59, Visits: 337
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]
Post #1060084
Posted Tuesday, February 08, 2011 2:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 28, 2011 1:17 AM
Points: 18, Visits: 49
To much failures, it's confusing, specially for the newbies!
Post #1060087
Posted Tuesday, February 08, 2011 3:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:08 AM
Points: 12,212, Visits: 9,193
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1060098
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse