Re: Query needed for this sol.

[Baron Schwartz]

Hi Abhishek,

In a table i have 100s of rows with six categories like A,B,C,D,E,F 
which is

marked with a column like
Table
ID Category
1   A
2   A
3  B
4   B
5  B
6 C
7 C

and so on 100 ids for each of six categories , i want to delete all 
but 10

ids of each category, i wanted to know how to do so,


This seems easier to solve if you re-word the problem in two steps:

1) find 10 rows in each category
2) delete all other rows

First define which 10 rows you want to keep.  I will assume you want to keep the 
ten 'A' rows with the GREATEST id number.


Query 1 then becomes:

select ID, Category
from tbl
where (
   select count(*) from tbl as f
   where f.Category = tbl.Category and f.ID > tbl.ID
) < 10;

Try that and see if it finds the rows you want to keep.

Now you can use what my colleague calls an 'exclusion join' to delete every 
other row.  Plug query 1 into this and you get Query 2:


delete t1.*
from tbl as t1
left outer join (
   -- same query as above
   select ID, Category
   from tbl
   where (
  select count(*) from tbl as f
  where f.Category = tbl.Category and f.ID > tbl.ID
   ) < 10
) as t2 using(ID, Category)
where t2.ID is null;

This is likely to run slowly on very large tables.  If you need this to be 
efficient, write back to the list again.


I hope this helps,
Baron

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query needed for this sol.

[Brent Baisley]

Not sure how to do it in a single query, but if you run this query repeatedly, 
eventually you'll have only 10 left of each category.

DELETE tableName
FROM tableName, (SELECT MAX(ID) deleteID,count(ID) categCount FROM tableName GROUP BY Category HAVING categCount>10) AS Smry WHERE 
tableName.ID=Smry.deleteID



- Original Message - 
From: "abhishek jain" <[EMAIL PROTECTED]>

To: 
Sent: Sunday, April 08, 2007 7:26 AM
Subject: Query needed for this sol.



Hi all,
I need a query for the fol. sol.
In a table i have 100s of rows with six categories like A,B,C,D,E,F which is
marked with a column like
Table
ID Category
1   A
2   A
3  B
4   B
5  B
6 C
7 C

and so on 100 ids for each of six categories , i want to delete all but 10
ids of each category, i wanted to know how to do so,

Pl. help
Thanks,
Abhishek jain




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Query needed for this sol.

[abhishek jain]

Hi all,
I need a query for the fol. sol.
In a table i have 100s of rows with six categories like A,B,C,D,E,F which is
marked with a column like
Table
ID Category
1   A
2   A
3  B
4   B
5  B
6 C
7 C

and so on 100 ids for each of six categories , i want to delete all but 10
ids of each category, i wanted to know how to do so,

Pl. help
Thanks,
Abhishek jain