Please do not multi-post. See my reply in .programming.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
[quoted text, click to view]
Hi, I have a table that contains orders from cliente. Suppose that each
client has more than one order, I would like to write a SELECT statement
that returns the order with the oldest date from each client.

for example:

Table:

idClient idOrder orderDate
35 100 03/10/2004
35 120 03/22/2004
35 150 04/10/2004
72 45 01/10/2004
72 56 01/29/2004

and the SELECT should return the next records:

idClient idOrder orderDate
35 150 04/10/2004
72 56 01/29/2004

Hope someone can help me with this :)
Thanks in advance
JC

Hi, I have a table that contains orders from cliente. Suppose that each
client has more than one order, I would like to write a SELECT statement
that returns the order with the oldest date from each client.

for example:

Table:

idClient idOrder orderDate
35 100 03/10/2004
35 120 03/22/2004
35 150 04/10/2004
72 45 01/10/2004
72 56 01/29/2004

and the SELECT should return the next records:

idClient idOrder orderDate
35 150 04/10/2004
72 56 01/29/2004

Hope someone can help me with this :)
Thanks in advance
JC

The technique you want to user here is called a corellated subquery. Try
this:

SELECT *
FROM tblOrder o
WHERE orderDate = (
SELECT MAX(orderDate)
FROM tblOrder o2
WHERE o.idClient = o2.idClient
)

I didn't implement your example so this may contain a typo. If it doesn't
work and you can't understand the solution then repost here as this technique
is going to be the most efficient solution.

PS: You question asks for the oldest date, but your example returns the most
recent date. My solution gives the most recent date.

[quoted text, click to view]