[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Group By Question

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how the fundamental information of how to create a table.
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

650 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