?
Solved

Group By Question

Posted on 2013-06-23
3
Medium Priority
?
764 Views
Last Modified: 2013-06-23
Hi All,

I need your advice on a SQL script.

I have a select statement selecting about 35 column from multiple tables with a where clause which is working fine. I need to add a MAX(a.date) to the query but I get the error below which I need to add a group by .

ORA-00937: not a single-group group function

I have added all the 35 columns to the group by and query seems to be ok.

My question is, is there a better way to run the query than to add the 35 columns to the Group By like I have done. In the near future I may need to add additional columns.

Sample query I have:

Select a.column1, a.column2, a.cloumn3, b.column4, b.column5, c.column6,
Case where c.column6 is null then c.column6 =’Y’ else ‘N’ end as test,
....
....
....

where b.column5 = (:p_DATE)
0
Comment
Question by:jose11au
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1000 total points
ID: 39270077
adding the fields to the group by is correct

if you need to use aggregate function like max(), min() and so on then all the fields that are NOT involved in those aggregate functions should appear in the group by.

To provide a more exact answer would require looking at your query.
0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1000 total points
ID: 39270086
<using your query, and giving a SQL Server answer>

How many columns out of the 35 determine the uniqueness of what you are trying to Max()?
For example, if it's only three columns (say col1, col3, col15), you could create a subquery with those three, the Max(), then JOIN on the rest of the query, like this..

Select a.col1, a.col2, a.col3, a.colgoo, a.colboo, a.colfoo, a.col35, b.max_date
FROM YourTable a
   JOIN (
       Select col1, col3, col15, Max(colWhatever) as max_date
       FROM YourTable
       GROUP BY col1, col3, col15 ) b ON a.col1 = b.col1 AND a.col3 = b.col3 AND a.col15 = b.col15
0
 

Author Comment

by:jose11au
ID: 39270091
Thanks so much for the information guys.

Cheers
0

Featured Post

How To Reduce Deployment Times With Pre-Baked AMIs

Even if we can't include all the files in the base image, we can sometimes include some of the larger files that we would otherwise have to download, and we can also sometimes remove the most time-consuming steps. This can help a lot with reducing deployment times.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

752 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