SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL DISTINCT on Multiple Columns


SQL DISTINCT on Multiple Columns

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216130 Visits: 41986
Andy DBA (2/8/2011)
I did not find this to be a good introductory article on DISTINCT. I gave this article 1 star and I have "the nads to say why".


That's more like it. And with that, I have to apologize to everyone... for one reason or another, I stopped reading just before the aggregate section of the article. It's not a good excuse, just the reason. Thanks for you and some of the others having "the nads", Andy. :-)

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216130 Visits: 41986
@Sarvesh,

My recommendation at this point would be to rework the article so it doesn't have the data error with the postal code in the first line of test data and correct the table name error in the SELECT of that same block of code. I'd also recommend that you re-research the aggregate section of the article and write your own example instead of copying from a post... especially since folks have been correct in that area. The two pieces of code are really nothing alike. I know that Steve Jones has no problem posting corrections.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Doug Bass
Doug Bass
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 52
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

This. Can it be corrected in the original article?
Kartik M Kumar
Kartik M Kumar
SSC Veteran
SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)

Group: General Forum Members
Points: 274 Visits: 241
Useful Article, in sense the author explain a new alternate of GROUP BY for DISTINCT ( Surely Unknown to many ).

For finding the Duplicates, the Query is Okay, but in many situation we are suppose to work on the duplicated data. So instead of Just check the Duplicates, we shall retrieve the total data by the below Query

-- To Check Duplicate Record

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

-- To Retrieve all records identified as Duplicate by some values

SELECT Address1, Address2, City, Postcode
FROM AddressTable a
WHERE (SELECT COUNT(*) FROM AddressTable b
WHERE a.Address1 = b.Address1 and a.Address2 = b.Address2
and a.City = b.City and a.Postcode = b.Postcode
) >1


May be useful to some one.....:-D

Thanks & Regards, Kartik M Kumar..
Adam Gojdas
Adam Gojdas
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 1451
Not to pick on the article too much further since it appears much has been said already but to me it seems the first sentence is off and that always starts me wondering about the whole article and content.

I would think this:
The DISTINCT clause works in combination with SELECT and gives you unique date from a database table or tables. The syntax for DISTINCT is show below

Was meant to be this:
The DISTINCT clause works in combination with SELECT and gives you unique data from a database table or tables. The syntax for DISTINCT is shown below

Granted I understand that in many cases the author's first language may not be English but when the very first sentence of the article is flawed in some obvious way I really do wonder about the rest of it. I always wonder how well it was proofed or edited if the very first sentence was seemingly missed.

Edit: Whistling Well, to be precise, it is the first 2 sentences.
:-D Laugh
dave hants
dave hants
Old Hand
Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)

Group: General Forum Members
Points: 359 Visits: 543
I frequently need to examine data in tables when I am developing new stored procedures. Often times because the user departments wanting the new programs don't really fully understand their own requirements, or the complex business data that is held in our tables.

One of the things I frequently do is to use DISTINCT, and to use GROUP BY, and to use COUNT(*). This is all part of the preliminary requirements gathering and me confirming with users about the make up of their data. And showing them views of their data they've never seen before.

So, for example, we may have a column containing a particular range of values. But no one knows what the values are or how frequently the same value is repeated for the rows. Typically a row will represent one transaction, or one deal, or one contract, or one part number/manufacturing material, or one customer.. etc.

So I will typically use the following to see how many distinct values have been used by the business:

select
distinct
unit_of_measure
from mfg.materials

So this will show all the different units that have been used by the factory.

Then I want to see specifically how many of each unit. So I'll use:

select
--distinct
unit_of_measure, count(*) as mycount
from mfg.materials
group by unit_of_measure
order by mycount desc

This is just an example out of many cases.

(In case you're wondering why I bother with the distinct and don't just go straight to the group by.... but that's a whole other story.)

HTH.
majorbloodnock
majorbloodnock
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2495 Visits: 3064
Andy DBA (2/8/2011)
....Please sell me on any reason beside "clarity of code" or "easy solution" (read band-aid fix) where using SELECT DISTINCT would be preferable to using GROUP BY.

Any time I need to populate a dropdown list with values from a more widely used table, I'll be likely to use SELECT DISTINCT instead of GROUP BY. It gives me the same answer, uses less code and won't need to be expanded to include aggregate functions. That's just one example off the top of my head.

Semper in excretia, sumus solum profundum variat
Neeraj Prasad Sharma
Neeraj Prasad Sharma
Mr or Mrs. 500
Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)

Group: General Forum Members
Points: 527 Visits: 675
Thato (2/8/2011)
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.


please explain this ..

Neeraj Prasad Sharma
Sql Server Tutorials
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