Hi,
This will do:
select name,status, max(_date) from dog
where status <(select max(status) from dog where status=dog.status) group
by name;
Thanks
ViSolve DB Team.
----- Original Message -----
From: "bruce" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Friday, January 05, 2007 10:23 AM
Subject: db/query question...
hi...
a further test...
the following test tbl/information:
dog
name char
status int
_date timestamp
id int
test data
dog
name status _date id
tom 1 01/20/07 1
tom 2 01/21/07 2
sue 1 01/20/07 3
tom 3 01/22/07 4
sue 2 01/21/07 5
bob 1 01/20/07 6
tom 4 01/23/07 7
sue 3 01/22/07 5
sue 4 01/23/07 5
sue 3 01/24/07 5
if i do a regular group, i can get (for tom)
tom 1 01/20/07 1
tom 2 01/21/07 2
tom 3 01/22/07 4
tom 4 01/23/07 7
sue 1 01/20/07 3
sue 2 01/21/07 5
sue 3 01/22/07 5
sue 4 01/23/07 5
sue 3 01/24/07 5
here's the tricky part. if i want to get the row with the status=3, but
only
if there's not a status=4 that has a later date, how do i accomplish
this...??
so, for tom, i would return 'null', and for sue, i'd return '3' for the
'01/24/07' the date for the last '3' is later than the date for the last
'4'...
any thoughts/comments..
thanks
-bruce
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
