• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

SQL select query

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.
0
hconant
Asked:
hconant
  • 5
  • 3
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
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.
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
;

Open in new window

0
 
hconantAuthor Commented:
Why would I need a JOIN?  Its all in one table?
0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
hconantAuthor Commented:
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.
??
0
 
Kevin CrossChief Technology OfficerCommented:
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
;

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
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
;

Open in new window

0
 
hconantAuthor Commented:
OK. That worked.  Thanks again.
0
 
Kevin CrossChief Technology OfficerCommented:
You are most welcome!
Best regards and happy coding,
Kevin
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now