help with a SELECT

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
JCTry:
select
o.*
from
Orders o
join
(
select
idCLient
, max (orderDate) orderDate
from
Orders
group by
idCLient
) x on x.idClient = o.idClient
and x.orderDate = o.orderDate
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"J.C. Developer" <smartconsultANTISPAM@.hotmail.com> wrote in message
news:ehBUo$rHFHA.2784@.TK2MSFTNGP09.phx.gbl...
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|||Thanks Tom, worked fine... sorry for multi posting
JC
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uweVaEsHFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Try:
> select
> o.*
> from
> Orders o
> join
> (
> select
> idCLient
> , max (orderDate) orderDate
> from
> Orders
> group by
> idCLient
> ) x on x.idClient = o.idClient
> and x.orderDate = o.orderDate
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "J.C. Developer" <smartconsultANTISPAM@.hotmail.com> wrote in message
> news:ehBUo$rHFHA.2784@.TK2MSFTNGP09.phx.gbl...
> 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
>
>|||Tiny detail - the specs were to retrieve the oldest order
for each client, not the newest, so you'll want MIN, not
MAX.
Another solution is
select o.*
from Orders as O1
where orderDate = (
select min(orderDate)
from Orders as O2
where O2.idClient = O1.idClient
)
One or the other formulation may be faster, but what
I like about this one is that it follows the English description
closely: select all orders where the order date is
the earliest for that client.
Note that there may not be just one earliest order
for a given client, and each of these queries will
return all orders with minimum orderDate for the client.
Steve Kass
Drew University
Tom Moreau wrote:

>Try:
>select
> o.*
>from
> Orders o
>join
>(
> select
> idCLient
> , max (orderDate) orderDate
> from
> Orders
> group by
> idCLient
> ) x on x.idClient = o.idClient
> and x.orderDate = o.orderDate
>
>|||Good catch!
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Steve Kass" <skass@.drew.edu> wrote in message
news:uWqRoPsHFHA.1860@.TK2MSFTNGP15.phx.gbl...
Tiny detail - the specs were to retrieve the oldest order
for each client, not the newest, so you'll want MIN, not
MAX.
Another solution is
select o.*
from Orders as O1
where orderDate = (
select min(orderDate)
from Orders as O2
where O2.idClient = O1.idClient
)
One or the other formulation may be faster, but what
I like about this one is that it follows the English description
closely: select all orders where the order date is
the earliest for that client.
Note that there may not be just one earliest order
for a given client, and each of these queries will
return all orders with minimum orderDate for the client.
Steve Kass
Drew University
Tom Moreau wrote:

>Try:
>select
> o.*
>from
> Orders o
>join
>(
> select
> idCLient
> , max (orderDate) orderDate
> from
> Orders
> group by
> idCLient
> ) x on x.idClient = o.idClient
> and x.orderDate = o.orderDate
>
>|||Thanks Steve, you're right...MIN is the choice for the oldest date. It
worked fine too.
JC
"Steve Kass" <skass@.drew.edu> wrote in message
news:uWqRoPsHFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Tiny detail - the specs were to retrieve the oldest order
> for each client, not the newest, so you'll want MIN, not
> MAX.
> Another solution is
> select o.*
> from Orders as O1
> where orderDate = (
> select min(orderDate)
> from Orders as O2
> where O2.idClient = O1.idClient
> )
> One or the other formulation may be faster, but what
> I like about this one is that it follows the English description
> closely: select all orders where the order date is
> the earliest for that client.
> Note that there may not be just one earliest order
> for a given client, and each of these queries will
> return all orders with minimum orderDate for the client.
> Steve Kass
> Drew University
> Tom Moreau wrote:
>