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 «««23456»»

HAVING Expand / Collapse
Author
Message
Posted Monday, June 21, 2010 8:18 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, March 27, 2014 10:20 AM
Points: 73, Visits: 125
Perhaps a different version of the question where the answer would be different with the Having clause vs just the where clause might help to clear up any confusion (mine anyways)?
Post #940350
Posted Monday, June 21, 2010 9:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:59 PM
Points: 5,797, Visits: 8,017
Rick Lang (6/21/2010)
Perhaps a different version of the question where the answer would be different with the Having clause vs just the where clause might help to clear up any confusion (mine anyways)?
If you go back to page 4, you will see that I posted a script to create and populate the table. Copy and paste it into SSMS, run it, then try changing things and see how that affects the results.

If you then still have questions, feel free to ask!



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #940386
Posted Monday, June 21, 2010 9:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, March 27, 2014 10:20 AM
Points: 73, Visits: 125
Ooops, Sorry. Went on vacation and didn't read through the whole thread.
Post #940392
Posted Monday, June 21, 2010 11:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:53 PM
Points: 11,168, Visits: 10,938
Rick Lang (6/21/2010)
Perhaps a different version of the question where the answer would be different with the Having clause vs just the where clause might help to clear up any confusion (mine anyways)?

In addition to Hugo's example, this is a favourite of mine:
SELECT  'One row'
WHERE 1 = 0
HAVING 0 = 0;





Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #940752
Posted Tuesday, June 22, 2010 1:04 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:56 AM
Points: 566, Visits: 1,582
That's just plain wrong.

-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible.
Post #940786
Posted Tuesday, June 22, 2010 1:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:59 PM
Points: 5,797, Visits: 8,017
Paul White NZ (6/21/2010)
Rick Lang (6/21/2010)
Perhaps a different version of the question where the answer would be different with the Having clause vs just the where clause might help to clear up any confusion (mine anyways)?

In addition to Hugo's example, this is a favourite of mine:
SELECT  'One row'
WHERE 1 = 0
HAVING 0 = 0;

Wow!
You should have saved that for QotD, Paul!



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #940801
Posted Tuesday, June 22, 2010 3:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:53 PM
Points: 11,168, Visits: 10,938
John McC (6/22/2010)
That's just plain wrong.

It still gives me nightmares from time to time
I used to think I understood the basics of SQL.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #940849
Posted Tuesday, June 22, 2010 3:09 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:00 AM
Points: 1,659, Visits: 6,006
I've seen that one before somewhere.
It becomes slightly more obvious if you add a 'count(*)' to the select list
Post #940853
Posted Tuesday, June 22, 2010 3:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:53 PM
Points: 11,168, Visits: 10,938
Hugo Kornelis (6/22/2010)
Wow!
You should have saved that for QotD, Paul!

Scary isn't it!

I blame a fellow MVP of yours: Rob Farley for all this - he reminded me of it in a T-SQL Tuesday blog a little while ago. I didn't have the link to hand before, but I've found it now, for anyone else that's interested:

http://msmvps.com/blogs/robfarley/archive/2010/01/12/t-sql-tuesday-having-puzzle-answer.aspx

I don't recall where I first came across it - it just seems to spring up every few years to remind me that SQL doesn't always go hand in hand with common sense...




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #940855
Posted Tuesday, June 22, 2010 3:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:53 PM
Points: 11,168, Visits: 10,938
Toreador (6/22/2010)
It becomes slightly more obvious if you add a 'count(*)' to the select list

Yep there's lots of variations:
SELECT  'One row', SUM(CASE WHEN 1 = 0 THEN 0 ELSE 1 END)
WHERE 1 = 0;





Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #940858
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse