Is There a More Efficeient Query Approach? (1 Viewer)

Steve R.

Retired
Local time
Today, 09:00
Joined
Jul 5, 2006
Messages
4,617
The intent of the two queries below is to retrieve the most recent magazine issue number ($maxissuenum). The code below works. The question is if there is a more efficient approach.

The first query returns the most recent date. The second query returns the maxium primary key (the issue number) for that speciic date. The maxium primary key in itself does NOT indicate the most recent issue, since the magazine data can be entered out-of-sequence.


Code:
        // ------------------------------------------------------------------------------
	// Get Most Recent Issue Date.					    
	$query = "SELECT MAX(IssueDate) FROM tblIssueList";
	$result =$conn->query($query);  
	if (!$result) die($conn->error);
	// Note use of "MYSQL_ASSOC" and  "MYSQLI_NUM"  
	$row=mysqli_fetch_array($result, MYSQLI_NUM);
	$maxdate=$row[0];
	// ------------------------------------------------------------------------------
	// Get Max Issue Number for most Recent Date
	$query = "SELECT MAX(IssueIDNUM) FROM tblIssueList WHERE IssueDate = '$maxdate'";
	$result =$conn->query($query);  
	if (!$result) die($conn->error);
	// Note use of "MYSQL_ASSOC" and  "MYSQLI_NUM";  
	$row=mysqli_fetch_array($result, MYSQLI_NUM);
	$maxissuenum=$row[0];
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:00
Joined
Feb 19, 2013
Messages
16,553
you can combine the two queries

Code:
SELECT MAX(IssueIDNUM) 
FROM tblIssueList 
WHERE IssueDate = (SELECT MAX(IssueDate) FROM tblIssueList AS T)

or

Code:
SELECT MAX(IssueIDNUM) 
FROM tblIssueList INNER JOIN (SELECT MAX(IssueDate) AS maxDate FROM tblIssueList) AS T ON tblIssueList.IssueDate=T.MaxDate

Which may be slightly quicker
 

Steve R.

Retired
Local time
Today, 09:00
Joined
Jul 5, 2006
Messages
4,617
Worked!! In retospect, I should have realized that approach. Thanks very much.
 

Users who are viewing this thread

Top Bottom