Next pint is on me Peter;-) The following turned out to be exactly
what I needed:
SELECT s1.id
FROM status_histories s1
WHERE s1.type = 'StatusHistoryOrder'
AND s1.code = 1
AND s1.created_at = (SELECT MAX(s2.created_at)
FROM status_histories s2
WHERE s1.order_id = s2.order_id)
And yes my MAX function does seem to work.
Thanks a ton!
Tim
On Dec 8, 2006, at 10:51 AM, Peter Bradley wrote:
Ysgrifennodd Tim McIntyre:
I tried that Peter and for some reason it's still selecting the
oldest date not the newest??? Seems odd? Also I'd really like to
just select id because I'll be using this in a subselect.
Thanks!
Tim
SELECT s1.order_id
FROM status_histories s1
WHERE s1.type = 'StatusHistoryOrder'
AND s1.created_at = (SELECT MAX(s2.created_at)
FROM status_histories s2
WHERE s1.order_id = s2.order_id)
?????????????????
If the MAX aggregate function isn't giving you the latest date, you
might try:
SELECT MAX(created_at)
FROM status_histories
to check whether that gives you the most recent date.
If it doesn't, there's something wrong with the data typing
somewhere, I would guess.
I suppose you could try MIN(), just to see what it gives you ...
Peter
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Be who you are and say what you feel, because those who mind don't
matter and those who matter don't mind.
- Dr. Seuss
