AV1611 Posted October 23, 2007 Share Posted October 23, 2007 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 Quote Link to comment https://forums.phpfreaks.com/topic/74509-solved-delete-each-max-item-with-group-by/ Share on other sites More sharing options...
AV1611 Posted October 23, 2007 Author Share Posted October 23, 2007 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) Quote Link to comment https://forums.phpfreaks.com/topic/74509-solved-delete-each-max-item-with-group-by/#findComment-376557 Share on other sites More sharing options...
AV1611 Posted October 23, 2007 Author Share Posted October 23, 2007 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 Quote Link to comment https://forums.phpfreaks.com/topic/74509-solved-delete-each-max-item-with-group-by/#findComment-376574 Share on other sites More sharing options...
clearstatcache Posted October 24, 2007 Share Posted October 24, 2007 would you mind if you show a sample of your database data.... Quote Link to comment https://forums.phpfreaks.com/topic/74509-solved-delete-each-max-item-with-group-by/#findComment-376876 Share on other sites More sharing options...
AV1611 Posted October 24, 2007 Author Share Posted October 24, 2007 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() Quote Link to comment https://forums.phpfreaks.com/topic/74509-solved-delete-each-max-item-with-group-by/#findComment-377067 Share on other sites More sharing options...
clearstatcache Posted October 25, 2007 Share Posted October 25, 2007 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) Quote Link to comment https://forums.phpfreaks.com/topic/74509-solved-delete-each-max-item-with-group-by/#findComment-377549 Share on other sites More sharing options...
teng84 Posted October 25, 2007 Share Posted October 25, 2007 delete tablename where id in(select field from table2 where filed2 = max(field3) group by field) Quote Link to comment https://forums.phpfreaks.com/topic/74509-solved-delete-each-max-item-with-group-by/#findComment-377551 Share on other sites More sharing options...
AV1611 Posted October 25, 2007 Author Share Posted October 25, 2007 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? Quote Link to comment https://forums.phpfreaks.com/topic/74509-solved-delete-each-max-item-with-group-by/#findComment-377875 Share on other sites More sharing options...
teng84 Posted October 25, 2007 Share Posted October 25, 2007 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 Quote Link to comment https://forums.phpfreaks.com/topic/74509-solved-delete-each-max-item-with-group-by/#findComment-378239 Share on other sites More sharing options...
clearstatcache Posted October 26, 2007 Share Posted October 26, 2007 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 | +-----------------+-----------+---------+------------+---------+ Quote Link to comment https://forums.phpfreaks.com/topic/74509-solved-delete-each-max-item-with-group-by/#findComment-378371 Share on other sites More sharing options...
AV1611 Posted November 1, 2007 Author Share Posted November 1, 2007 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! Quote Link to comment https://forums.phpfreaks.com/topic/74509-solved-delete-each-max-item-with-group-by/#findComment-382953 Share on other sites More sharing options...
Barand Posted November 3, 2007 Share Posted November 3, 2007 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 Quote Link to comment https://forums.phpfreaks.com/topic/74509-solved-delete-each-max-item-with-group-by/#findComment-384007 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.