Avoiding WHERE IN

This query seems to run particularly slowly, is there a way to rewrite
it without using IN?
id fruit_name
14 Apple
34 Apple
37 Banana
56 Banana
66 Banana
78 Pear
103 Orange
select * from my_table
where id in (
select max(id)
from my_table
group by fruit_name )
Result
==================
id fruit_name
34 Apple
66 Banana
78 Pear
103 Orange
Cheers!SELECT A.* FROM MyTable A
INNER JOIN
(SELECT max(id) as Id FROM MyTable) B
ON A.id = B.Id
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
<davidol@.hushmail.com> wrote in message
news:1109567370.806709.130280@.o13g2000cwo.googlegroups.com...
> This query seems to run particularly slowly, is there a way to rewrite
> it without using IN?
> id fruit_name
> 14 Apple
> 34 Apple
> 37 Banana
> 56 Banana
> 66 Banana
> 78 Pear
> 103 Orange
> select * from my_table
> where id in (
> select max(id)
> from my_table
> group by fruit_name )
> Result
> ==================
> id fruit_name
> 34 Apple
> 66 Banana
> 78 Pear
> 103 Orange
>
> Cheers!
>|||An index on (fruit_name) or perhaps (fruit_name, id) or (fruit_name, id
desc)
will help, I think.
You can also try some alternative was of writing this.
select fruit_name, max(id)
from my_table
group by fruit_name
select * from my_table
where not exists (
select * from my_table as mtcopy
where mtcopy.fruit_name = my_table.fruit_name
and mtcopy.id > my_table.id
)
select fruit_name, id
from my_table
where id = (
select top 1 id
from my_table as mtcopy
where mtcopy.fruit_name = my_table.fruit_name
order by id desc
)
Steve Kass
Drew University
davidol@.hushmail.com wrote:

>This query seems to run particularly slowly, is there a way to rewrite
>it without using IN?
>id fruit_name
>14 Apple
>34 Apple
>37 Banana
>56 Banana
>66 Banana
>78 Pear
>103 Orange
> select * from my_table
> where id in (
> select max(id)
> from my_table
> group by fruit_name )
>Result
>==================
>id fruit_name
>34 Apple
>66 Banana
>78 Pear
>103 Orange
>
>Cheers!
>
>|||SELECT MAX(id), fruit_name
FROM my_table
GROUP BY fruit_name
However, this probably won't do much to speed up the query. Can you give
actual DDL, including any indexes? (See http://www.aspfaq.com/5006
On 2/28/05 12:09 AM, in article
1109567370.806709.130280@.o13g2000cwo.googlegroups.com,
"davidol@.hushmail.com" <davidol@.hushmail.com> wrote:

> This query seems to run particularly slowly, is there a way to rewrite
> it without using IN?
> id fruit_name
> 14 Apple
> 34 Apple
> 37 Banana
> 56 Banana
> 66 Banana
> 78 Pear
> 103 Orange
> select * from my_table
> where id in (
> select max(id)
> from my_table
> group by fruit_name )
> Result
> ==================
> id fruit_name
> 34 Apple
> 66 Banana
> 78 Pear
> 103 Orange
>
> Cheers!
>