SQL DISTINCT on Multiple Columns

Question

SQL DISTINCT on Multiple Columns

Viewing 8 posts - 31 through 37 (of 37 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

@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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

xxxxxxxxxxxxx SSC Journeyman Points: 83 More actions · Answer 3

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 SSChasing Mays Points: 644 More actions · Answer 4

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 SSCommitted Points: 1766 More actions · Answer 5

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.

😀 :laugh:

dave hants Default port Points: 1485 More actions · Answer 6

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 SSCrazy Eights Points: 9389 More actions · Answer 7

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, suus solum profundum variat

Neeraj Prasad Sharma Ten Centuries Points: 1315 More actions · Answer 8

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