Group by and Where clause

Question

Group by and Where clause

sanandh87

Mr or Mrs. 500

Points: 578
More actions
March 3, 2013 at 9:47 am

#269268

Hi,
Please see below,
Table name : Log_table
Columns: Logid int, databasename varchar(30), tablename varchar(30), logdate datetime
Query 1:
Select max(logdate) from log_table
where databasename='db1' and tablename='table1';
consider the Log_table is empty, when I execute Query 1, the result will be 'Null'.
Query 2 :
select max(logdate) from log_table
Where databasename='db1' and tablename='table1'
group by databasename,tablename;
Consider the log_table is empty, when I execute Query 2, the result will be empty.
Note : both the condition table is empty.
Please explain me what happening inside and why the result different?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

In the first query, you give SQL Server no choice. You say to find the MAX unconditionally for the given criteria and there isn't one so it returns a NULL.

In the second query, it's different. You're saying find the MAX for a given group which is actually a condition. There are no groups that match your criteria so it returns nothing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sanandh87 Mr or Mrs. 500 Points: 578 More actions · Answer 2

Thanks for your reply Jeff.

I got clear pic .. thanks a lot...

one more doubt Jeff,

both the condition table is empty,

then why the where condition returns 'null'

and

group by condition returns nothing.

can you plz explain me ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

http://sqlblog.com/blogs/paul_white/archive/2012/03/12/fun-with-aggregates.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass