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 Tuesday, February 8, 2011 8:39 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:47 PM
Points: 35,770, Visits: 32,436
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."

(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 #1060785
Posted Tuesday, February 8, 2011 8:45 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:47 PM
Points: 35,770, Visits: 32,436
@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."

(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 #1060786
Posted Wednesday, February 9, 2011 7:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 1, 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
Posted Wednesday, February 9, 2011 10:43 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, December 14, 2014 11:17 PM
Points: 89, Visits: 161
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.....


Thanks & Regards,
Kartik M Kumar..
Post #1061361
Posted Wednesday, February 9, 2011 12:27 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:46 PM
Points: 79, Visits: 1,331
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: Well, to be precise, it is the first 2 sentences.
Post #1061481
Posted Wednesday, February 9, 2011 3:34 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 5:31 AM
Points: 82, Visits: 424
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
Posted Thursday, February 10, 2011 3:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 17, 2014 6:00 AM
Points: 1,049, Visits: 3,012
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
Posted Wednesday, July 10, 2013 12:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 27, 2013 5:37 AM
Points: 306, Visits: 524
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
Post #1471977
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse