Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.
COURSE of the MONTH
11 days, 1 hour left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL select query
Posted on 2011-09-09
I have the following very simply query
Select PropertyId, contractorid, max(InspectionDate)
From InspectionReport
group by PropertyId
This gives me a simple list. But when I try to add additional columns in the select statement I get an error about grouping. This is a simple table with some properties, with some dates, and some other columns. You will notice the max(inspectionDate). Each property has a bunch of date related events recorded and this query gets the record with the most recent event. But why can't I get the rest, or some more of the columns to show? I am sure this is simple. I am just learning SQL queries. Thanks. Hope its not too dump a question.
Select PropertyId, contractorid, max(InspectionDate)
From InspectionReport
group by PropertyId
This gives me a simple list. But when I try to add additional columns in the select statement I get an error about grouping. This is a simple table with some properties, with some dates, and some other columns. You will notice the max(inspectionDate). Each property has a bunch of date related events recorded and this query gets the record with the most recent event. But why can't I get the rest, or some more of the columns to show? I am sure this is simple. I am just learning SQL queries. Thanks. Hope its not too dump a question.
[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
5-
3
8 Comments
Active 4 days ago
MySQL often doesn't complain about that as evidenced by the contractorid not being in the aggregate; however, I would advise you to put all the non-aggregated columns into the group by or into an aggregate unless you can find a way to make the data unique.
For example, if you are trying to bring back data that corresponds to the MAX(InspectionDate), then this is an alternative.
For example, if you are trying to bring back data that corresponds to the MAX(InspectionDate), then this is an alternative.
SELECT PropertyId, contractorid, InspectionDate
FROM InspectionReport ir
JOIN (
SELECT PropertyId
, MAX(InspectionDate) AS MAX_InspectionDate
FROM InspectionReport
GROUP BY PropertyId
) ir_max ON ir_max.propertyid = ir.propertyid
AND ir_max.MAX_InspectionDate = ir.InspectionDate
;
Active 4 days ago
Look at what the JOIN is doing. You can do this in a WHERE clause. Just my personal preference to use a derived table. Typically works with the caveat that your dates are unique. If they are not, then you would get duplicate rows matching MAX(). If that is the case, then you can do this using ranking.
I wrote an Article on that here:
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_1555-Analytical-SQL-Where-do-you-rank.html
You would rank the data by the InspectionDate descending and then select the top 1 row for each. You will have to use some secondary column(s) in the ranking to determine how to resolve conflicts or collisions in max date.
I wrote an Article on that here:
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_1555-Analytical-SQL-Where-do-you-rank.html
You would rank the data by the InspectionDate descending and then select the top 1 row for each. You will have to use some secondary column(s) in the ranking to determine how to resolve conflicts or collisions in max date.
I have one smalll problem with your query. In the first SELECT, PropertyId is showing an ambiguous column name. It is fine in the lines below using same name. The result set does not have the PropertyId showing, only contractorId and Inspection date. I can list the PropertyId in a simple query.
SELECT PropertyId From InspectionReport. I've trible checked for typos and even picked the column name from the list.
??
SELECT PropertyId From InspectionReport. I've trible checked for typos and even picked the column name from the list.
??
Active 4 days ago
Forgot the aliases.
SELECT ir.PropertyId, ir.contractorid, ir.InspectionDate
FROM InspectionReport ir
JOIN (
SELECT PropertyId
, MAX(InspectionDate) AS MAX_InspectionDate
FROM InspectionReport
GROUP BY PropertyId
) ir_max ON ir_max.propertyid = ir.propertyid
AND ir_max.MAX_InspectionDate = ir.InspectionDate
;
Active 4 days ago
Another way to fix it is to name the PropertyId in the derived table something different.
SELECT PropertyId, contractorid, InspectionDate
FROM InspectionReport ir
JOIN (
SELECT PropertyId AS ID
, MAX(InspectionDate) AS MAX_InspectionDate
FROM InspectionReport
GROUP BY PropertyId
) ir_max ON ir_max.id = ir.propertyid
AND ir_max.MAX_InspectionDate = ir.InspectionDate
;
Featured Post
August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
- Query Syntax
- SQL
- Databases
- MySQL Server
- MongoDB
- NoSQL Databases, Percona
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month11 days, 1 hour left to enroll
770 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.