June 7, 2010 at 11:53 pm
Really an amazing and challenging Question.. Gud Job!:-)
------------------------
~Niths~
Hard Work never Fails 🙂
June 8, 2010 at 3:49 am
Nice one... really unusual.
June 8, 2010 at 4:04 am
Excellent question, Hugo.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 8, 2010 at 7:43 am
Ah well. Got it wrong but at least learned something new.
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:
June 8, 2010 at 7:46 am
Hugo,
You're getting a reputation for outstanding QotD's (at least in my small circle). Thanks for taking the time to do them.
This one particularly exercised the brain. That's a good thing. 🙂
June 8, 2010 at 8:03 am
Thanks, all, for the kind words.
There are five more in the pipeline, and some are simmering in the back of my head; I hope those won't disappoint you.
June 14, 2010 at 1:47 am
Hi,
the answer should be 1 which is not one of the available values so I selected one of the wrong options given.
What is asked is Select Count(*) which will give the count of records found satisfying the given condition and the not the value in the record. So, clearly to me the query is
Select Count(*)
From QotD
Where Col2 <> 4
which will give answer as 5(as there are 5 records satisfying this condition)
And applying another filter using Having clause as below:
Having MAX(Col2) > 5
will leave the user with only 1 record that is the last one with value 6 in the Col2 column.
Therfore the Count(*) will be 1.
Unfortunately, given Answer options are not containing the right one.
Correct me if I am missing something here.
June 14, 2010 at 2:39 am
You are missing something.
The Having clause behaves as a where clause in this query. The maximum of col2 is 6, so each row sattisfies the condition, ergo, the having clause doesn't do anything. The where clause filters out the row with value 4 for col2, leaving you with 5 rows. If you count those rows, you'll get one row displaying the result 5.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 14, 2010 at 3:14 am
sa_pachori (6/14/2010)
Hi,the answer should be 1 which is not one of the available values so I selected one of the wrong options given.
What is asked is Select Count(*) which will give the count of records found satisfying the given condition and the not the value in the record. So, clearly to me the query is
Select Count(*)
From QotD
Where Col2 <> 4
which will give answer as 5(as there are 5 records satisfying this condition)
And applying another filter using Having clause as below:
Having MAX(Col2) > 5
will leave the user with only 1 record that is the last one with value 6 in the Col2 column.
Therfore the Count(*) will be 1.
Unfortunately, given Answer options are not containing the right one.
Correct me if I am missing something here.
Thanks for your comment.
You are indeed missing something. The HAVING clause does not filter individual rows, but whole groups. If there is no GROUP BY clause, the HAVING clause will treat all rows that are not filterered out by the WHERE clause as one single group. So the 5 rows that match the WHERE clause, {(1, 1) (1, 2), (1, 3), (2, 5), (2,6)} are one group. The MAX(Col2) of that group is 6; the HAVING clause is satisfied and the group is not filtered out. The COUNT(*) in the SELECT then counts the rows in the group and returns 5.
I deliberately postes the question in a format that discourages copy/paste, but in case you want to try for yourself, here is the code in a copy/paste-able format:
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;
June 17, 2010 at 4:01 am
wware (6/7/2010)
Putting MAX(col2) in the result set helped me see what was going on. WHERE clause evaluated first giving one row result set. This one row satisfies HAVING clause.select COUNT(*), MAX(col2)
from @QotD
where col2 <> 4
having MAX(col2) > 5
Good explanation. It clearly describes how the result set was derived. 🙂
June 21, 2010 at 8:18 am
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)?
June 21, 2010 at 9:03 am
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!
June 21, 2010 at 9:06 am
Ooops, Sorry. Went on vacation and didn't read through the whole thread.
June 21, 2010 at 11:18 pm
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 22, 2010 at 1:04 am
That's just plain wrong. 😉
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:
Viewing 15 posts - 31 through 45 (of 55 total)
You must be logged in to reply to this topic. Login to reply