SQL DISTINCT on Multiple Columns

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Sarvesh Singh » SQL DISTINCT on Multiple Columns

37 posts, Page 4 of 4

«««1 2 3 4

SQL DISTINCT on Multiple Columns

Rate Topic

Display Mode

Topic Options

Author

Message

Jeff Moden

Posted Tuesday, February 08, 2011 8:39 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:01 PM
Points: 33,108, Visits: 27,033

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

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1060785

Jeff Moden

Posted Tuesday, February 08, 2011 8:45 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:01 PM
Points: 33,108, Visits: 27,033

@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

Post #1060786

Doug Bass

Posted Wednesday, February 09, 2011 7:45 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, June 01, 2011 8:25 AM
Points: 15, 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?

Post #1061140

Kartik M

Posted Wednesday, February 09, 2011 10:43 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, May 27, 2013 10:50 PM
Points: 85, Visits: 135

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

Thanks & Regards,
Kartik M Kumar..

Post #1061361

Adam Gojdas

Posted Wednesday, February 09, 2011 12:27 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, March 22, 2013 3:37 PM
Points: 79, Visits: 1,317

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

Post #1061481

david.moule

Posted Wednesday, February 09, 2011 3:34 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, June 06, 2013 1:14 PM
Points: 72, Visits: 347

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.

Post #1061601

majorbloodnock

Posted Thursday, February 10, 2011 3:28 AM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 10:52 AM
Points: 1,043, Visits: 2,945

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

Post #1061806

« Prev Topic | Next Topic »

37 posts, Page 4 of 4

«««1 2 3 4

Permissions