This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.
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 today
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 today
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 today
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 today
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
Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Creating and Managing Databases with phpMyAdmin in cPanel.
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 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 Month15 days, 2 hours left to enroll
649 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.