ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.
One of a set of tools we're offering as a way to say thank you for being a part of the community.
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.
5-
3
8 Comments
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
;
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.
??
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
;
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
Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
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 Month10 days, 19 hours left to enroll
608 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.