No, I want simply the ID of the single record with the highest MailingDate value.
ok. good to know.
Yes, MailingDate is an integer and it is an INT(10) field
good
No, I didn't use any grouping functions unless that's what MAX() is
yes. that is what max() is. it is known as a grouping, group by, aggregation or aggregate function. it and its co-functions work across sets of records. this is the root of your issues.
However, my follow-up posting to the original question is giving me the needed results
as I posted above - it may not always give you the desired results unless the newsletter mailing date is guaranteed always to be unique.
it seems overly complicated for something that should be simple
yes, although not terribly so, particularly if the mailingdate column is indexed. see the end of the post for why this type of solution may be the right solution in certain circumstances.
I still don't see why my original query was giving the mismatch between ID and MailingDate values
for the reasons i have posted several times. you have no group by clause.
from the manual
manual said:
If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows.
Read further and in more detail:
here
I think we can give this a rest now
you may feel that the topic is closed for you (in which case no need to reply) but this site is for all readers and so it may be useful to continue the thread and provide examples.
so here goes
consider this simple table
Code:
CREATE TABLE `newsletters` (
`rowID` int(10) NOT NULL AUTO_INCREMENT,
`ID` int(10) NOT NULL,
`mailingdate` int(12) NOT NULL,
PRIMARY KEY (`rowID`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=latin1
then populate it with random data. I used 1000 rows populated with the following php script
Code:
<?php
date_default_timezone_set('UTC');
try{
$pdo = new PDO( 'mysql:host=127.0.0.1;dbname=don;port=8889',
'root',
'root');
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
$s = $pdo->prepare("Insert into newsletters (rowID, ID, mailingdate) VALUES (?,?,?)");
if(!$s):
die(print_r($pdo->errorinfo(), true));
endif;
for($i=0; $i<1000; $i++):
$params = array( NULL,
floor(rand(1,10)),
strtotime("+$i hours"));
$s->execute($params);
endfor;
now let's consider some result sets
Code:
SQL query: SELECT MAX(MailingDate), ID FROM newsletters ;
MAX(MailingDate) ID
1388798277 1
so, the mailing date provided is indeed the highest in the table, but the value for ID is meaningless as it is simply the first in the table. there is no group by clause (or equivalent where/having).
going back to your actual requirement - to find the ID with the highest mailingdate integer.
this can be achieved with an aggregate function. taking it one step at a time
Code:
SELECT ID, mailingdate
FROM newsletters
group by ID
having max(mailingdate)
this returns a recordset of each ID and the latest mailing date for that ID.
this could equally be achieved without a having clause
Code:
SELECT ID, max(mailingdate)
FROM newsletters
group by ID
the important thing here is the group by clause. without that a single row would get returned, typically of the first value for ID and the maximum mailingdate.
now, to retrieve JUST the ID with the highest mailing date, continuing with the group by functions one approach might be to order the recordset
Code:
SELECT ID, mailingdate
FROM newsletters
group by ID
order by mailingdate desc
now we know that the ID is in the first row. we can limit the recordset to just the first row too, of course
Code:
SELECT ID,mailingdate
FROM newsletters
group by mailingdate desc, ID
LIMIT 1
None of these aggregations are necessary, however, in your case. My post of 22 Nov 13 @ 15:53 refers. Just select the whole recordset, order by mailingdate and limit.
Code:
Select ID
from newsletters
order by mailingdate desc
limit 1
the casting I posted earlier was incorrect. I should, instead, have used from_unixtime
Code:
select id, from_unixtime(mailingdate)
from newsletters
order by mailingdate desc
limit 1
but none of the above provide for the scenario where there is more than one row for a given mailing date - for example a mail-shot. so let's alter the recordset to provide for this circumstance. I used this php script to do so (it selects 5 rows at random and boots mailingdate long into the future)
Code:
date_default_timezone_set('UTC');
$pdo = new PDO( 'mysql:host=127.0.0.1;dbname=don;port=8889',
'root',
'root');
$range = range(1,1000);
shuffle($range);
$query = "update newsletters set mailingdate=? where rowID=?";
$s = $pdo->prepare($query);
$now = strtotime("+1 year");
for($i=0; $i<5; $i++):
$r = $s->execute(array($now, $range[$i]));
endfor;
Code:
SELECT group_concat(id), from_unixtime(mailingdate)
from newsletters
group by mailingdate desc
limit 1
this provides a comma separated list in the first returned column.
if you want the recordset as a normal unconcatenated set then you need to revert to your solution and use joined tables
Code:
SELECT id
FROM newsletters n1
JOIN (
SELECT MAX( mailingdate ) AS md
FROM newsletters
)n2
ON n1.mailingdate = n2.md
this is relatively efficient. on my system, over a 1000 records, the query time on an unindexed column was 0.012 seconds and, once indexed, 0.0005 seconds. showing the difference indexing makes.
i see no particular difference between the explicit join and the implicit join-in-the-where that you employed. the former is preferable to me because it shows what is going on. but that's a personal style choice. a modern version of mysql will almost certainly optimise both the same way.
i trust this clarifies for future readers.
