banner
RustyNail

RustyNail

coder. 【blog】https://rustynail.me 【nostr】wss://ts.relays.world/ wss://relays.world/nostr
HomeArchives

Remove duplicates in MySQL

80
AI Translation
This post is translated from Chinese into English through AI.View Original
AI-generated summary
This is a SQL code that selects the id and koto from a table called hitokoto, where koto is not duplicated and only the row with the smallest id is kept. The code may run slowly.

Direct code

SELECT
    id,
    koto 
FROM
    hitokoto 
WHERE
    koto IN (SELECT t1.koto FROM hitokoto t1 GROUP BY t1.koto HAVING COUNT(1) > 1) 
    AND id NOT IN (SELECT MIN(id) FROM hitokoto t1 GROUP BY t1.koto HAVING COUNT(1) > 1)

Remove the same koto and leave the smallest id.

Find all rows that are the same, find the smallest ones, then not in.

Seems a bit slow.

Loading...
Ownership of this post data is guaranteed by blockchain and smart contracts to the creator alone.