Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Assuming that the <code>date</code> field is always incremental, you can follow the next procedure</p> <ol> <li>Track the duplicate records and the minimum date of these records</li> <li>Delete all the records that have date values greater than the minimum date.</li> </ol> <p>A code example:</p> <p>Step 1:</p> <pre><code>select name, points, count(id) as rowCount, min(id) as minId, min(`date`) as minDate from yourTable where points = 0 group by name having count(id)&gt;1 </code></pre> <p>Step 2:</p> <pre><code>delete from yourTable where id in ( select id from yourTable inner join ( select name, points, min(id) as minId, count(id) as rowCount, min(`date`) as minDate from yourTable where points = 0 group by name having count(id) &gt; 1 ) as a on yourTable.name = a.name and yourTable.id &gt; a.minId ) and points = 0; </code></pre> <p>Hope this helps</p> <hr> <p>I think it might be useful to use temp tables to get the ids you want to delete:</p> <pre><code>-- Step 1: Create a temporary table with the names of the people you want to remove drop table if exists temp_dup_names; create temporary table temp_dup_names select name, points, min(id) as minId, count(id) as rowCount, min(`date`) as minDate from yourTable where points = 0 group by name having count(id) &gt; 1; alter table temp_dup_names add index idx_name(name), add unique index idx_id(minId); -- Step 2: Create a temporary table with the ids you want to delete drop table if exists temp_ids_to_delete; create temporary table temp_ids_to_delete select distinct a.id from yourTable as a inner join temp_dup_names as b on a.name=b.name and a.id &gt; b.minId where points = 0; alter table temp_ids_to_delete add unique index idx_id(id); -- Step 3: Delete the rows delete from yourTable where id in (select id from temp_ids_to_delete); -- If MySQL is configured in 'safe mode', you may need to add this -- to the where condition: -- and id &gt; 0; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POHow to delete duplicate rows in this case?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USBarranka
    1. USBarranka
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI do not understand why, but it did not work. Deleted many records, but still left many of the same user 0, in sequence.
      singulars
      1. PO
      1. USGuttemberg
    2. COare both `id` and `date` fields incremental? (i.e. a greater `id` value is paired with a greater `date` value). I've edited the post to show a step-by-step solution using temp tables. Check step 2: The temp table should have the Ids you want to delete. If it doesn't have the right Ids, then step 1 must be checked again
      singulars
      1. PO
      1. USBarranka
    3. COYes, id and date are incremental
      singulars
      1. PO
      1. USGuttemberg
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload