getting the last result

[James Scott]

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

[Bill Karwin]

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.

[onedbguru]

or...

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

[onedbguru]

or...

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

[Rich Ryan]

<on*******@firstdbasource.com> wrote in message
news:11*********************@j55g2000cwa.googlegro ups.com...

or...

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

This won't work. read the OPs requirements.

Rich

[Rich Ryan]

"James Scott" <js************@nospmahoo.com> wrote in message
news:IM*****************@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