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 ««12

WHERE vs. GROUP BY again Expand / Collapse
Author
Message
Posted Monday, January 28, 2013 10:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 28, 2013 11:40 AM
Points: 45, Visits: 79
Sorry...
Post #1412559
Posted Monday, January 28, 2013 10:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 28, 2013 11:40 AM
Points: 45, Visits: 79
Fine!
Are the folowing two SELECT statements equvalent (i.e. -- see my original question, -- "return exactly the same row set" no matter "what data is")?
SELECT xx FROM yy WHERE xx >=5 GROUP BY xx
SELECT xx FROM yy GROUP BY xx HAVING xx >=5
Post #1412562
Posted Monday, January 28, 2013 11:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 4:28 PM
Points: 2,027, Visits: 3,023
Igor Makedon (1/28/2013)
Fine!
Are the folowing two SELECT statements equvalent (i.e. -- see my original question, -- "return exactly the same row set" no matter "what data is")?
SELECT xx FROM yy WHERE xx >=5 GROUP BY xx
SELECT xx FROM yy GROUP BY xx HAVING xx >=5



NO, they are not equivalent, atlhough they may return the same result set.

Since xx is a non-aggregate column, "WHERE xx ..." is correct.

In some SQLs, SQL will not check xx with HAVING until after the GROUPing is done. SQL Server now effectively rewrites the second query to match the first. But it's wrong to rely on that. I would suspect the second query is not even guaranteed to be accepted by SQL itself as a valid query -- might depend on the SQL engine and/or version.


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1412576
Posted Monday, January 28, 2013 11:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 28, 2013 11:40 AM
Points: 45, Visits: 79
My question is: just "may" [sometimes]? or "always [any data] do"?
Post #1412583
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse