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

HAVING without GROUP BY Expand / Collapse
Author
Message
Posted Monday, August 30, 2010 8:37 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 5,797, Visits: 8,015
Comments posted to this topic are about the item HAVING without GROUP BY


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #977669
Posted Monday, August 30, 2010 9:25 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 14, 2014 5:28 PM
Points: 329, Visits: 608
Nice one.
Post #977675
Posted Monday, August 30, 2010 9:43 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 2:13 AM
Points: 1,866, Visits: 368
thanks for this wonderful question...
Post #977680
Posted Monday, August 30, 2010 10:32 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:46 PM
Points: 381, Visits: 535
Nice. Helps if I remember that 6 > 5
Post #977687
Posted Monday, August 30, 2010 11:59 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 05, 2013 11:51 PM
Points: 488, Visits: 336
Thanks Hugo - this is an awesome question! Tests fundamentals and has appropriate choices as well... Pls keep posting such gems!

Saurabh Dwivedy
___________________________________________________________

My Blog: http://tinyurl.com/dwivedys

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

Be Happy!
Post #977702
Posted Tuesday, August 31, 2010 1:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:30 AM
Points: 5,797, Visits: 8,015
Toby Harman (8/30/2010)
Nice. Helps if I remember that 6 > 5

I feel your pain. I managed to miss the point as well - on my own question!
Thanks everyone for the nice words!

For those who want to see for themselves, or try playing around with the issue, here is some repro code:
CREATE TABLE #QotD (Col1 int, Col2 int);
go
INSERT INTO #QotD (Col1, Col2)
SELECT 1, 1 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 2, 4 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 2, 6;
go
SELECT COUNT(*)
FROM #QotD
WHERE Col2 <> 4
HAVING MAX(Col2) < 5;
go
DROP TABLE #QotD;
go




Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #977721
Posted Tuesday, August 31, 2010 2:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, April 13, 2014 10:27 PM
Points: 117, Visits: 454
Very good question.. I missed my points but learnt something new today..
Post #977748
Posted Tuesday, August 31, 2010 3:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 7:04 PM
Points: 176, Visits: 563
Another good one. I have been losing points lately but I don't miss them.

{Sorry, I meant I was losing, not getting, points lately but its alright. Now I hope to remember if I get an SQL gotcha in work for possible QOTD. }
Post #977782
Posted Tuesday, August 31, 2010 3:27 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:05 AM
Points: 1,528, Visits: 5,172
I was expecting an answer of 3 or 5, but I wasn't sure which, so I created a temporary table to check--was somewhat surprised to see no rows at all returned, but now Hugo has explained the answer I see the logic.
Post #977785
Posted Tuesday, August 31, 2010 5:57 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 821, Visits: 833
Great question. I was torn between the correct answer and the single record returning 0... I don't mind having gotten it wrong.

ron


-----
a haiku...

NULL is not zero
NULL is not an empty string
NULL is the unknown
Post #977857
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse