Solved

Group By Question

Posted on 2013-06-23
3
729 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Check ALL SP in database make sure there are no errors 17 46
SQL Query 2 34
MS SQL SERVER and ADODB.commands 8 23
Oracle Nested table uses ? 2 36
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.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to take different types of Oracle backups using RMAN.
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.

820 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