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:
>

help required :- Table space management in sql server 2000

Hi ,

if we have two file group in a particular sql server 2000 database (c and d drive), and in that database suppose one particular table (location c drive) is growing very fast, i want to move it to D: drive file group. so how we can do it.

Thanks

Shiva

You can create or rebuild an existing clustered index on the table specifying the other filegroup. The data and the clustered index always reside on the same filegroup so creating or rebuilding the clustered index on the other filegroup will force the data over to the other filegroup.

-Sue