470,586 Members | 1,379 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,586 developers. It's quick & easy.

getting the last result

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
May 26 '06 #1
5 4530
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.
May 26 '06 #2
or...

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

May 26 '06 #3
or...

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

May 26 '06 #4

<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
May 26 '06 #5

"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
May 26 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Kali K E | last post: by
303 posts views Thread by mike420 | last post: by
8 posts views Thread by Franz Steinhaeusler | last post: by
reply views Thread by Kenneth Keeley | last post: by
5 posts views Thread by henrycortezwu | last post: by
36 posts views Thread by Chuck Faranda | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.