Keeping the 10 newest records... in each GROUP ?

Ok, I give up.
I need to GROUP BY state... keep the newest 10 records in each state...
then delete all other records.
(Why do the simplest *SOUNDING* queries... always stump me?)
CREATE TABLE MyTable
(
MyKey Int
IDENTITY(1,1) PRIMARY KEY,
MySaveDate DateTime NOT NULL
DEFAULT GetDate(),
MyState VarChar(2) NOT NULL
)
GOHi
I'm not sure that understand your request
SELECT OrderID,CustomerId ,OrderDate
FROM Orders WHERE OrderDate IN (SELECT TOP 2 OrderDate FROM
Orders O WHERE O.CustomerId=Orders.CustomerId ORDER BY OrderDate DESC)
ORDER BY CustomerId
This is an example from NorthWind database. We get two newest OrderId made
by each Customer
Can you show us DDL+ sample data + expected result?
""A_Michigan_User"" <Michigan_RE_M0VE@.ameritech.net> wrote in message
news:e0VNlMdsFHA.276@.TK2MSFTNGP10.phx.gbl...
> Ok, I give up.
> I need to GROUP BY state... keep the newest 10 records in each state...
> then delete all other records.
> (Why do the simplest *SOUNDING* queries... always stump me?)
> CREATE TABLE MyTable
> (
> MyKey Int IDENTITY(1,1) PRIMARY KEY,
> MySaveDate DateTime NOT NULL DEFAULT
> GetDate(),
> MyState VarChar(2) NOT NULL
> )
> GO
>
>|||On Mon, 5 Sep 2005 00:34:43 -0400, "A_Michigan_User" wrote:

>Ok, I give up.
> I need to GROUP BY state... keep the newest 10 records in each state...
>then delete all other records.
>(Why do the simplest *SOUNDING* queries... always stump me?)
>CREATE TABLE MyTable
> (
> MyKey Int
>IDENTITY(1,1) PRIMARY KEY,
> MySaveDate DateTime NOT NULL
>DEFAULT GetDate(),
> MyState VarChar(2) NOT NULL
> )
> GO
Hi A_Michigan_User,
If you want to leave the table untouched but remove some rows from the
output, use the code Uri posted.
But if your requirement is to delete rows from the table, but keep only
the 10 most recent rows for each state, use the one below (note: this
version may retain more than 10 rows for a state if there are ties)
DELETE FROM MyTable
WHERE (SELECT COUNT(*)
FROM MyTable AS b
WHERE b.MyState = MyTable.MyState
AND b.MySaveDate > MyTable.MySaveDate) >= 10
Note: this code is untested. Test it on a backed-up database, inside a
transaction and ROLLBACK if the results are wrong.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)