Solved

Group By Question

Posted on 2013-06-23
3
710 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
3 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 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 65

Assisted Solution

by:Jim Horn
Jim Horn earned 250 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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now