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 123»»»

Distinct Expand / Collapse
Author
Message
Posted Monday, May 6, 2013 8:42 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
Comments posted to this topic are about the item Distinct



Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #1449968
Posted Monday, May 6, 2013 11:12 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, December 14, 2014 11:09 PM
Points: 1,962, Visits: 2,406
Thanks Vinay for the question...
I was not aware of the thing that if we use DISTINCT then Column names specified in the ORDER BY clause, must be defined in the select list.
Learned some basic thing



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1449977
Posted Monday, May 6, 2013 11:38 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 9:20 AM
Points: 1,372, Visits: 1,567
Thanks for the easy one Vinay, after tricky Qotd yesterday

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1449983
Posted Monday, May 6, 2013 11:39 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
Lokesh Vij (5/6/2013)
Thanks for the easy one Vinay, after tricky Qotd yesterday

Qotd yesterday




Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #1449984
Posted Monday, May 6, 2013 11:41 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
kapil_kk (5/6/2013)
Thanks Vinay for the question...
I was not aware of the thing that if we use DISTINCT then Column names specified in the ORDER BY clause, must be defined in the select list.
Learned some basic thing


Thank Kapil. This question solved its purpose.




Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #1449986
Posted Monday, May 6, 2013 11:46 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 3:20 PM
Points: 18,064, Visits: 16,099
What, no controversy for this question?

Thanks Vinay




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1449989
Posted Tuesday, May 7, 2013 1:24 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:18 AM
Points: 1,057, Visits: 2,359
Classic;

Thank you for the post, so simple and yet it keeps us busy getting it correct.

There is one SQL batch (a report data) where I used DISTINCT on all for the final sql statement, just to make sure that no data is getting repeated (even though the batch provides the unique records); and I was wondering rather than using DISTINCT, better to use GROUP BY (with no calculation, just to make the records unique), later when I tested the actual execution plan I came to know the even GROUP BY makes a logical distinct sort... then I again changed it back to DISTINCT.

I guess, when we DISTINCT, it also sorts the data physically, so usage of additional ORDER BY is not needed, like depending on the column list we mention in the SELECT.

SELECT  DISTINCT City, Country FROM #Table
SELECT DISTINCT Country, City FROM #Table



ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
Post #1450011
Posted Tuesday, May 7, 2013 1:33 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 12:30 AM
Points: 1,804, Visits: 783
Good question, and after yesterdays made me re read it to see how it was run

Hope this helps...

Ford Fairlane
Rock and Roll Detective





Post #1450012
Posted Tuesday, May 7, 2013 3:01 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 10:51 AM
Points: 4,158, Visits: 5,556
This was a good question, thanks.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1450035
Posted Tuesday, May 7, 2013 6:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:36 PM
Points: 6,133, Visits: 8,398
Thanks! Good question.

In addition to the explanation given: the restriction actually makes sense. Remember that, logically, the ORDER BY is performed *after* the SELECT. In the official ANSI standard, columns in the ORDER BY must always be in the SELECT clause. The fact that we usually can order by other columns is because, logically, SQL Server adds a hidden extra column in the SELECT, uses it for ordering, then doesn't disply it or return it to the client. (And I stress that this is "logically" - queries are not actually executed that way!). But with a SELECT DISTINCT, that is impossible. Adding the extra column before the DISTINCT would influence the results (the extra columns could make rows that are otherwise the same distinct to each other, and once removed the results would show duplicate rows in spite of the DISTINCT). And adding the extra column after the DISTINCT is impossible, since a single row after DISTINCT can correspond to multiple rows before DISTINCT.

Or, yet another way to put it, suppose I have this data in a table called MyTable:
Col1 | Col2
-----+-----
1 | a
2 | b
3 | a

Now suppose I were allowed to execute this query:
SELECT DISTINCT Col2
FROM MyTable
ORDER BY Col1;

The resultset should contain an "a" and a "b". But in what order? The "b" comes from the row with Col1 = 2, but the "a' comes from two rows, with Col1 = 1 and Col1 = 3. Should the "a" go before or after the "b"? There is no possible answer for this - and that's why the query is illegal!


Raghavendra Mudugal (5/7/2013)
I guess, when we DISTINCT, it also sorts the data physically, so usage of additional ORDER BY is not needed

WRONG!!!!!!
This may or may not work correctly, but you have no guarantee.
The optimizer had different ways to implement a DISTINCT. One of them is a "Distinct sort" - where rows are sorted and duplicate are removed. That would produce results in the specified output - though the optimizer could decide to reverse the order of the columns. Another way would be to convert the DISTINCT to an aggregate - remember that there is no difference at all between "SELECT DISTINCT Col1 FROM MyTable" and "SELECT Col1 FROM MyTable GROUP BY MyCol", and the optimizer knows that. If the aggregeate is implemented with a stream aggregate operator. you may still be okay (though, again, the order of the columns if more than one is used can be changed to match an existing index and avoid a sort step). But if a hash aggregate operator is used, you're completely hosed.
And then, with a large enough table, you can get a parallel plan, where each individual stream might or might not have the rows in order, but the order is not retained when gathering streams. Or, with a complex query, the optimizer might decide to push down the distinct operator as far as possible to reduce the rows, and then reorder the results coming out of it for the rest, e.g. to facilitate a merge join, or as a byproduct of a hash join.

When working with SQL Server, if you need guarantees about the order in which results will be returned - ALWAYS USE AN ORDER BY!!!!!
Everything else means you rely on undocumented, and hence unguaranteed behaviour. A ticking timebomb!



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1450106
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse