[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Group by on a select statement, SQL 2005

Posted on 2009-12-18
4
Medium Priority
?
273 Views
Last Modified: 2012-08-13
I have a query that is acting really funny. I am joining two tables.  When I use a where clause everything comes back fine.  When I add a group by clause I get no results.  Is there a setting that I am missing?   Also, If i run the statement with set ansi_nulls off it works.  I am not using any isnull or comparing any nulls.  I am basically selecting columns.  If I remove the group by it works.

select table1.column1
from table1
inner join table2
  on table1.column1 = table2 = column2
group by .....
0
Comment
Question by:yanci1179
  • 2
4 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26082566
not sure what exactly your issue is unless you privide us the  exact queries and sample results

select table1.column1
from table1
inner join table2
  on table1.column1 = table2.column2
group by  table1.column1
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26082569
where and Group by, both are different in functionality, one for filtering and the other for Grouping ,
0
 

Author Comment

by:yanci1179
ID: 26082623
Please see the attached queries.  I have attached the ones that work and those that do not work
weirdQueries.sql
0
 
LVL 3

Accepted Solution

by:
vipin_nagarro earned 2000 total points
ID: 26082779
Hi
May be one of the column in a table u are using for join have no values, which will return nothing when ANSI_NULL is ON and will return it if u set it off.
This is a data problem, check the data in all the columns u r using of all the tables in query.
The Story here is :
The SQL-92 standard requires that an equals (=) or not equal to (<>) comparison against a null value evaluates to FALSE. When SET ANSI_NULLS is ON, a SELECT statement using WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement using WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the SQL-92 standard. A SELECT statement using WHERE column_name = NULL returns the rows with null values in column_name. A SELECT statement using WHERE column_name <> NULL returns the rows with nonnull values in the column.
Refer: http://doc.ddart.net/mssql/sql70/set-set_5.htm
Thanks and Regards
Vipin Goel
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question