Remove duplicate data by day. If the value is 0, 0 is used. Otherwise, the maximum value is used. If the value is 0

Remove duplicate data by day. If the value is 0, 0 is used. Otherwise, the maximum value is used. If the value is 0

Test data: mysql> select * from t2; + ---- + -------- + --------------------- + ---------- + | id | userid | inputDate | infoStatus | + ---- + -------- + --------------------- + ---------- + | 1 | 1 | 00:00:00 | 20013 | 2 | 1 | 00:00:00 | 0 | 3 | 2 | 00:00:11 | 20015 | 4 | 2 | 00:00:22 | 20013 | 5 | 2 | 00:00:00 | 0 | 6 | 2 | 00:00:00 | 20013 | 7 | 2 | 00:00:00 | 0 | 8 | 2 | 00:00:00 | 0 | 9 | 2 | 00:00:00 | 20013 | 10 | 2 | 00:00:00 | 0 | 11 | 2 | 00:00:00 | 0 | + ---- + -------- + ----------------------- + ------------ + query SQL: select id, userid, date (inputDate) as date, max (infoStatus) as m, min (infoStatus) as I, if (min (infoStatus) = 0, max (infoStatus )) as max from t2 where infoStatus = 0 or infoStatus <= 99999 group by date; receipt result: + ---- + -------- + ------------ + ------- + | id | userid | date | m | I | max | + ---- + -------- + ------------ + ------- + | 1 | 1 | 2014-07-11 | 20013 | 0 | 0 | 3 | 2 | 2014-07-12 | 20015 | 20013 | 20015 | 9 | 2 | 20013 | 0 | 0 | 5 | 2 | 2014-07-14 | 0 | 0 | 0 | 6 | 2 | 2014-07-15 | 20013 | 0 | 0 | 8 | 2 | 2014-07-16 | 0 | 0 | 0 | + ---- + -------- + ------------ + ------- + my idea is to group by time, at the same time, the query is the smallest, followed by the largest. If the smallest is 0, then the max field is 0; otherwise, the largest one is obtained.

 

Remove duplicate excel Data. Only one value is retained. can I remove 0 by the way? Using the formula

If the data is in A1: A1000, B2.
= T (INDEX (A: A, MATCH (, 0/IF (COUNTIF (B $1: B1, A $1: A $1000) = 0, ROW (A $1: A $1000 )),)))
At the same time, press CTRL + SHIFT + ENTER to ENTER the array formula, and then use the auto-fill handle to copy the formula down.

Filter data in excel. do not duplicate data, but take the maximum value in the second column of repeated data.

Change the date format, replace "." with "-" with the format of yyyy-mm-dd, and sort the data in descending order of the date. Add A secondary column and enter the formula COUNTIF ($ A $1: A1, A1). Column A is the number. If the formula is equal to 1, it indicates that no duplicates or duplicate numbers correspond to the maximum time.