[SOLVED] delete each MAX() item with group by

[AV1611]

I need to delete the max number from refdesttl for the grouping below.  That doesn't work...

 

Hope that's enough info...

 

delete  from refdesbyqtr where

 

Max(refdesbyqtr.refdesttl)

 

Group By

 

refdesbyqtr.partnum,

 

refdesbyqtr.defecttype,

 

refdesbyqtr.refdes

[AV1611]

this didn't work either

 

delete  from refdesbyqtr

 

(Select

 

refdesbyqtr.partnum,refdesbyqtr.partdescr,refdesbyqtr.defecttype,refdesbyqtr.refdes,

 

Max(refdesbyqtr.refdesttl) as MAX_FAIL,refdesbyqtr.failcode,refdesbyqtr.fadescr

 

From refdesbyqtr

 

Group By refdesbyqtr.partnum,refdesbyqtr.defecttype)

[AV1611]

Let me ask the question in a different way.

 

This is a query that I ran to create a new table:

create table rdqout

Select

refdesbyqtr.partnum,refdesbyqtr.partdescr,refdesbyqtr.defecttype,refdesbyqtr.refdes,

Max(refdesbyqtr.refdesttl) as MAX_FAIL,refdesbyqtr.failcode,refdesbyqtr.fadescr

From refdesbyqtr

Group By refdesbyqtr.partnum,refdesbyqtr.defecttype

 

Now what I want to do is delete the records from the original table that were copied.

 

That should be more helpful to the nice person that helps me out

[clearstatcache]

would you mind if you show a sample of your database data....

[AV1611]

I am just pulling the max() record based on the grouping above.  When I copy the record out, I want to remove it so I can do the same thing again for the new max(), which become the #2 in the other table, then I'll just sum() the rest so I end up with a new table that is the #1, #2, and sum(all the rest)

 

I'm doing some trend analysis for some component failure data.  That is why the grouping is the way it is.  I'm grouping by The assy number, the quarter, and the component ID.

 

If there is another way to do this I'm all ears.  I know how to sum(), I know how to max(), I can't think of another way to come up with second_to_max()

 

 

[clearstatcache]

how about doing this in two queries.....

  * first is the query that will get the max value and while doing this u get the value(s) of the unique field(s) for the row with the max value...

  * in the second query delete the row using the value(s) of the unique field(s)

[teng84]

delete tablename where id in(select field from table2 where filed2 = max(field3) group by field)

[AV1611]

You both have the right answer... but it won't work... I'll have to do this with PHP I guess.  The problem is the table does not have an index field (Don't flame me!!!)

 

how would I alter the table via a single query to

a.  add the field (I can do that already)

b.  add the auto-incrementing index number so I can do what you suggest?

[teng84]

 

b.  add the auto-incrementing index number so I can do what you suggest?

 

change your field as auto increment? or use the last insert id then ad one on it

[clearstatcache]

try ds 1....

 

delete t1

from refdesbyqtr as t1,

    (select max(refdesttl) as refdesttl, partnum, defecttype, refdes from refdesbyqtr group by partnum, defecttype, refdes) as t2

where t1.refdesttl=t2.refdesttl

    and t1.partnum=t2.partnum

    and t1.defecttype=t2.defecttype

    and t1.refdes=t2.refdes

 

assuming your table looks like this.....

+-----------------+-----------+---------+------------+---------+

| refdesbyqtr_key  | refdesttl    | partnum | defecttype | refdes    |

+-----------------+-----------+---------+------------+---------+

|              1        |        35    |      1    | defect1    | refdes1 |

|              2        |        56    |      1    | defect1    | refdes1 |

|              3        |        89    |      1    | defect1    | refdes1 |

|              4        |        54    |      1    | defect2    | refdes2 |

|              5        |        78    |      1    | defect2    | refdes2 |

|              6        |        62    |      2    | defect1    | refdes1 |

|              7        |        39    |      2    | defect1    | refdes2 |

|              8        |        71    |      2    | defect1    | refdes1 |

+-----------------+-----------+---------+------------+---------+

 

 

[AV1611]

I think that would do it, but I fell back on php and did it the other way.  Thanks, I'll sure keep that snippet!

 

1. I altered table to add index field

2. I copied Max() to new table

3. I deleted record from first table the had same id as new table

4. I did step 2 & 3 a second time

5. I did sum() on rest.

 

I ended up with a table with the top two items followed by the sum of all others.

 

That's what the customer wanted... Top and Rest were easy, getting the second to top sucked.  Not pretty, but it worked.

 

I bet Barand coulda done it in one query and popped out a graph, all in three lines of code... I ain't no Barand

 

Thanks all!

[Barand]

I bet Barand coulda done it in one query and popped out a graph, all in three lines of code... I ain't no Barand

 

... and leap tall buldings too?

 

I appreciate the faith, however misplaced.  I expect I'd have gone along the same lines as clearstatcache