Hi Claire,
On Mon, 23 Aug 2004 14:52:29 -0700 (PDT), Claire Lee <[EMAIL PROTECTED]> wrote:
> Hi,
> Here's a table of mine
>
> name date changeDate
> n1 d1 cd1
> n2 d1 cd3
> n2 d2 cd1
> n4 d1 cd2
> n1 d2 cd5
> n5 d1 cd4
> n6 d2 cd2
>
> I need to select every name for which the changeDate
> corresponding to d1 is greater than the changeDate
> corresponding to d2. Any way I can use one statement
> to do this?
Yes, of course. You seem to be suggesting that there will only be two
records with the same name in the table. In that case, something like
this (although this is untested):
select name
from mytable a
where changedate >
(select changedate
from mytable b
where a.name=b.name
and a.changedate != b.changedate);
or:
select name
from mytable a
where exists
(select * from mytable b
where a.name=b.name
and a.changedate > b.changedate);
I personally like the latter, form-wise. I suspect it might be
marginally faster, too.
If my above assumption isn't the case, we need more information on
what happens when there are 3 records (do you want any record which
has changedate greater than another record of the same name? or only
the highest? etc.).
Also, mind your NULLs. You might need to edit the above query
depending on how you want to handle NULLs in changedate.
Good luck,
--
Matt Warden
Miami University
Oxford, OH
http://mattwarden.com
This email proudly and graciously contributes to entropy.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
