?
Solved

Group By Question

Posted on 2013-06-23
3
Medium Priority
?
798 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 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

862 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