Solved

SQL select query

Posted on 2011-09-09
8
266 Views
Last Modified: 2012-05-12
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
Comment
Question by:hconant
  • 5
  • 3
8 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36514356
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
 

Author Comment

by:hconant
ID: 36514374
Why would I need a JOIN?  Its all in one table?
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36514385
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

Author Comment

by:hconant
ID: 36522330
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36522541
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36522554
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
 

Author Comment

by:hconant
ID: 36522569
OK. That worked.  Thanks again.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36522581
You are most welcome!
Best regards and happy coding,
Kevin
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MYSQL simple update statement 3 53
simple mysql statement 3 37
change database name 2 29
Restore of mysql database from .SQL file - using Coldfusion 5 36
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

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.

Join & Ask a Question