Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
byrmol
Shed Building SQL Farmer
1591 Posts |
 Posted - 2009-04-30 : 22:23:20
|
How many rows should be returned from this query.select 'Hello World ' , 1 where 42 is null union allselect 'Hello World MAX' , max(1) where 42 is null It returns 1 row.Why?It seems to affect all aggregation operators in 2000 and 2005 and I would imagine 2008.PS: Long time no see.DavidM |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-05-01 : 05:15:31
|
If you use MAX(2), which select does it return?Does the second statement return?
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-05-01 : 12:45:11
|
| Yep one row. It seems strange at first glance. But, that is fundemental to how aggregate functions work. You can also extend this by replacing MAX with SUM. MAX will return NULL (as in this example) where as SUM will return 0.To get your "expected" results try adding a HAVING clause: HAVING MAX(1) IS NOT NULLPlay with that for a bit and see if it makes sense. :) |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-05-04 : 04:15:58
|
| http://weblogs.sqlteam.com/jeffs/archive/2007/11/13/sql-aggregate-totals.aspxMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
Aggregation weirdness