How to use MAX() and Group to gather query results?
Experts,
I am struggling to find the correct syntax for my query.
For simplicity purposes, let's say my db table has 5 columns: id, Name, Date, Year, Month
I need to identify the last record in the db based on the MAX(Date) where the report Name='Activity Report'. What further complicates this query is the fact that I need to be able to grab all of the information from the DB row found so that I can print it to the screen later.
This is what I have so far:
SELECT MAX(Date) as most_recent, id, Name, Date, Year, Month FROM reports WHERE Name='Activity Report' ORDER BY Name ASC
I know this is not correct but, all of the examples I have seen just confuse me even more.
Can anyone assist?
I am struggling to find the correct syntax for my query.
For simplicity purposes, let's say my db table has 5 columns: id, Name, Date, Year, Month
I need to identify the last record in the db based on the MAX(Date) where the report Name='Activity Report'. What further complicates this query is the fact that I need to be able to grab all of the information from the DB row found so that I can print it to the screen later.
This is what I have so far:
SELECT MAX(Date) as most_recent, id, Name, Date, Year, Month FROM reports WHERE Name='Activity Report' ORDER BY Name ASC
I know this is not correct but, all of the examples I have seen just confuse me even more.
Can anyone assist?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Actually it is pretty inelegant, I threw it together fast. You could do the same thing using joins that would look better and probably be a little faster in execution...
That being said, the logic for both is the following:
1. Think in sets (you start with your whole table..just imagine that vaguely in your mind).
2. You need to get to your goal of - one row - where that row has the max id and max date for the set belonging to 'Activity Report'. So, let's first just imagine the one set of all the Activity Report rows.
3. Now that you have the Activity Report rows in mind, let's just trim out of that all the rows that have dates older than the most recent date (ie. MAX(Date)).
4. At this point the set in your mind is just the activity report rows with the most recent date. Think of this set.
5. Ask yourself, what is the largest id in this set? TheMAX(id) is. :)
That being said, the logic for both is the following:
1. Think in sets (you start with your whole table..just imagine that vaguely in your mind).
2. You need to get to your goal of - one row - where that row has the max id and max date for the set belonging to 'Activity Report'. So, let's first just imagine the one set of all the Activity Report rows.
3. Now that you have the Activity Report rows in mind, let's just trim out of that all the rows that have dates older than the most recent date (ie. MAX(Date)).
4. At this point the set in your mind is just the activity report rows with the most recent date. Think of this set.
5. Ask yourself, what is the largest id in this set? TheMAX(id) is. :)
My query nests each of those sets inside of parentheses.
ASKER
@sl8rz:
Thank you for explaining the query to me in verbal form. It makes sense to me.
I don't know if the query is executing properly though. The query now looks like this:
$get_finance_reports_sql = "SELECT * FROM controller_reports WHERE
id IN (SELECT MAX(id) FROM controller_reports WHERE Name='".$finance_favorites
Date IN (SELECT MAX(Date) FROM controller_reports WHERE Name='".$finance_favorites
This query is being used inside a while() loop which in this case produces 7 different variations of the variable $finance_favorites['report
The page takes forever to load now and I assume that this is due to the complexity of the query and the fact that it is looping 7 times through the first while loop. Knowing this...do you think that it would be better to use a GROUP syntax to run it?
My full code is below. (PS - Only shortened the code example because I didn't want to confuse my original question.
Thank you for explaining the query to me in verbal form. It makes sense to me.
I don't know if the query is executing properly though. The query now looks like this:
$get_finance_reports_sql = "SELECT * FROM controller_reports WHERE
id IN (SELECT MAX(id) FROM controller_reports WHERE Name='".$finance_favorites
Date IN (SELECT MAX(Date) FROM controller_reports WHERE Name='".$finance_favorites
This query is being used inside a while() loop which in this case produces 7 different variations of the variable $finance_favorites['report
The page takes forever to load now and I assume that this is due to the complexity of the query and the fact that it is looping 7 times through the first while loop. Knowing this...do you think that it would be better to use a GROUP syntax to run it?
My full code is below. (PS - Only shortened the code example because I didn't want to confuse my original question.
$get_finance_favorites_sql = "SELECT * FROM favorites WHERE user_nuid='".$user."' AND site='finance'";
$get_finance_favorites_query = mysql_query($get_finance_favorites_sql);
if(mysql_num_rows($get_finance_favorites_query)>=1){
echo'<div style="padding:0px 10px 10px 10px;">';
echo'<h2>Financial Package</h2>';
while($finance_favorites = mysql_fetch_array($get_finance_favorites_query)){
$get_finance_reports_sql = "SELECT * FROM controller_reports WHERE
id IN (SELECT MAX(id) FROM controller_reports WHERE Name='".$finance_favorites['report_name']."' AND
Date IN (SELECT MAX(Date) FROM controller_reports WHERE Name='".$finance_favorites['report_name']."'))";
$get_finance_reports_query = mysql_query($get_finance_reports_sql) or die(mysql_error());
while($finance_reports = mysql_fetch_array($get_finance_reports_query)){
echo $finance_reports['Name'];
echo '<br /><br />';
echo $finance_reports['Month'];
echo '<br /><br />';
echo $finance_reports['Year'];
echo '<br /><br />';
echo $finance_reports['id'];
}//END WHILE
}//END WHILE
echo'</div>';
}//END IFASKER
@sl8rz: & @all:
I was able to obtain the results I needed using a series of 3 queries and resulting while() statements. I understand that this is the long way to do things in this instance but, I was unable to get the query provided above to work.
Can I simplify what I have done using a more advanced query?
I was able to obtain the results I needed using a series of 3 queries and resulting while() statements. I understand that this is the long way to do things in this instance but, I was unable to get the query provided above to work.
Can I simplify what I have done using a more advanced query?
$get_finance_favorites_sql = "SELECT * FROM favorites WHERE user_nuid='".$user."' AND site='finance' ORDER BY report_name";
$get_finance_favorites_query = mysql_query($get_finance_favorites_sql);
if(mysql_num_rows($get_finance_favorites_query)>=1){
echo'<div style="padding-left:10px; float:left;">';
echo'<h2>Financial Package Favorites</h2>';
while($finance_favorites = mysql_fetch_array($get_finance_favorites_query)){
$get_finance_reports_sql = "SELECT MAX(Date) as recent_date FROM controller_reports
WHERE Name='".$finance_favorites['report_name']."'";
$get_finance_reports_query = mysql_query($get_finance_reports_sql) or die(mysql_error());
while($finance_reports = mysql_fetch_array($get_finance_reports_query)){
$get_it = "SELECT * FROM controller_reports WHERE Date='".$finance_reports['recent_date']."' AND Name='".$finance_favorites['report_name']."'";
$get_it_query = mysql_query($get_it) or die(mysql_error());
while($show_it = mysql_fetch_array($get_it_query)){
//SET THE TABLE CSS STYLE
if($style == 1){$tclass = "row1"; $style = 0;}
else{$tclass = "row2"; $style = 1;}
//END SET THE TABLE CSS STYLE
echo'<div id="favorite'.$show_it['id'].'" style="width:600px;">
<div align="center" style="width:45%; float:left;" class="'.$tclass.'">
<a href="controller_download.php?cmd=file&id='.$show_it['id'].'&nuid='.$user.'" title="Download" onclick="return confirm("Notice: You are about to view privileged information.\n\nPlease use discretion when sharing this information with your department. Please do not distribute or share this report with others whom do not already have access to this site.\n\nIf you have questions about this policy, please contact the DSA TPMG Controller\'s office at 925-295-7629.")">'.$show_it['Name'].'</a>
</div>
<div align="center" style="width:15%; float:left;" class="'.$tclass.'">'.$show_it['Month'].'</div>
<div align="center" style="width:8%; float:left;" class="'.$tclass.'">'.$show_it['Year'].'</div>
<div align="center" style="width:8%; float:left;" class="'.$tclass.'">
<form style="" id="favorite_form'.$show_it['id'].'" method="post">
<input name="site" type="hidden" value="finance" />
<input name="report_name" type="hidden" value="'.$show_it['Name'].'" />
<input name="user_nuid" type="hidden" value="'.$user.'" />
<input name="div_id" type="hidden" value="'.$show_it['id'].'" />
<input name="favorite" type="hidden" value="1" />
<input name="add_delete" type="hidden" value="delete" />
<img onclick="favorite(\'favorite_form'.$show_it['id'].'\', \'favorite'.$show_it['id'].'\')" title="Remove the '.$show_it['Name'].' report from Your Favorites" src="images/remove.png" border="0" style="cursor:pointer;" />';
echo'</form>';
echo'</div>';
echo'</div>';
}//END WHILE
}//END WHILE
}//END WHILE
echo'</div>';
}//END IF
Yuck! Whoa... slow down... writing all that PHP really isn't necessary. You want one record. You want the most recent date.
Try something like this, first:
This will return all colums from the table, order them by date, put them in descending order (Which, presumably, is the most recent) and then returns only one record. Try that. Far better to do stuff like that in SQL than in PHP.
Edit:
You could probably do this as well:
Try something like this, first:
SELECT id, Name, Date, Year, Month FROM reports WHERE Name='Activity Report' ORDER BY Date DESC LIMIT 1This will return all colums from the table, order them by date, put them in descending order (Which, presumably, is the most recent) and then returns only one record. Try that. Far better to do stuff like that in SQL than in PHP.
Edit:
You could probably do this as well:
SELECT id, Name, Date, Year, Month FROM reports WHERE Name='Activity Report' AND Date=MAX(Date) ORDER BY Date DESC LIMIT 1ASKER
Thank you very much. Sorry about the lag time here. Been off for a few weeks.
ASKER
Can I ask you to explain what the query is doing here? I think I understand it but, I just want to make sure?
Thanks.