Hello,

I have following table:
product| ordered_by | date
n1 | A | 2006-01-01
n1 | A | 2006-02-01
n1 | D | 2006-03-01
n1 | B | 2006-05-01
n2 | B | 2006-01-01
n2 | C | 2006-04-01
....

As a result I only want one set per product with the latest "ordered_by".
In this example:
n1, B and
n2, C

So far I solved it within the executing program but I was wondering if there
is an efficient query to do the same.

Thanks,
James

James Scott wrote:
> As a result I only want one set per product with the latest "ordered_by".
> In this example:
> n1, B and
> n2, C

SELECT t1.*
FROM tablename t1
LEFT OUTER JOIN tablename t2 ON t1.product = t2.product AND t1.`date` <
t2.`date`
WHERE t2.`date` IS NULL

In other words, "show me the row where there is no other row with the
same product and a greater date."

Regards,
Bill K.

or...

select product,order_by,max(date) from tablea group by
product,order_by;

or...

select product,order_by,max(date) from tablea group by
product,order_by;

<onedbguru [at] firstdbasource.com> wrote in message
news:1148669180.378862.31830 [at] j55g2000cwa.googlegroups.com...
> or...
>
> select product,order_by,max(date) from tablea group by
> product,order_by;
>

This won't work. read the OPs requirements.

Rich

"James Scott" <jscott_deltorg [at] nospmahoo.com> wrote in message
news:IMFdg.2468$W97.2349 [at] twister.nyroc.rr.com...
> Hello,
>
> I have following table:
> product| ordered_by | date
> n1 | A | 2006-01-01
> n1 | A | 2006-02-01
> n1 | D | 2006-03-01
> n1 | B | 2006-05-01
> n2 | B | 2006-01-01
> n2 | C | 2006-04-01
> ...
>
> As a result I only want one set per product with the latest "ordered_by".
> In this example:
> n1, B and
> n2, C
>
> So far I solved it within the executing program but I was wondering if
there
> is an efficient query to do the same.
>
> Thanks,
> James
>
>

If you have subqueries, this will do it:

select product
,orderedby
from [order] as o1
where orderdate = (
select max(orderdate)
from [order] as o2
where o2.product = o1.product)

Rich