[MySQL] Usuwanie powtarzających się elementów

Witam!
Mam np. taką tabelę:

rozszerzenie | rodzaj
----------------------
    exe      | program
    jpg      | grafika
    zip      | archiwum
    bmp      | grafika
    rar      | archiwum
    gif      | grafika

Teraz chciałbym usunąć z niej dublujące się rekordy wg. kolumny 'rodzaj', tak aby po operacji usuwania zostało tylko po 1 elemencie (nie jest ważne które pozostaną), czyli moja tabelka powinna wyglądać np. tak:

rozszerzenie | rodzaj
----------------------
    exe      | program
    bmp      | grafika
    rar      | archiwum

Myślałem o wykorzystaniu DISTINCT.. ale mi nie wyszło..

DELETE FROM Pliki WHERE (
SELECT DISTINCT rodzaj
FROM Pliki
);

aha to ma być jedno zapytanie może mieć podzapytania ale ma być jedno.

nie zrobisz tego w jednym zapytaniu - proponował bym stored proc

1. najpierw robisz zapytanie typu select rodzaj, count(rodzaj) from pliki group by rodzaj having count(rodzaj) > 1
2. potem w pętli robisz
   ile = count(rodzaj)
   rodz = rodzaj
   powyższe to dane z pierwszego zapytania
   delete from(pliki) where roz in (select rozsz from pliki where rodzaj = rodz limit ile - 1)

Dzięki.. ale chodzi o to żeby użyć tylko zapytań SQLa. Przy pomocy php to żaden problem.. Wiem, że można to zrealizować za pomocą jednego rozbudowanego zapytania (zawierającego oczywiście podzapytania). Wiem, bo mój prowadzący wymyślił to zadanie.. i jest ono do zrealizowania..

a zobacz to http://www.xaprb.com/blog/2006/10/11/how-to-delete-duplicate-rows-with-sql/

U mnie na Postgresie takie zapytanie daje oczekiwany rezultat:

DELETE FROM pliki PZ WHERE rozszerzenie !=
    (SELECT MIN(rozszerzenie) FROM pliki PW
     WHERE PW.rodzaj = PZ.rodzaj GROUP BY rodzaj)

Tak. MIN() jest zdefiniowane dla typów znakowych :)

Przepraszam, że rozwaliłem layout tą przydługą nazwą [wstyd]

// scaliłem posty, jako ze pisane z tego samego IP zmieniłem autora - m.Q

Misiekd napisał(a)

a zobacz to http://www.xaprb.com/blog/2006/10/11/how-to-delete-duplicate-rows-with-sql/

No jest tam rozwiązanie tylko za pomocą kilku zapytań.. :/

to nie rozwiązuje mojego problemu..

Potrafię wybrać elementy tak żeby pozostawić po 1 sztuce każdego.. ale nie potrafię tego usunąć :(

SELECT DISTINCT rodzaj FROM Pliki

Hmm. Próbowałeś mojego zapytania?

Tak próbowałem.. nie trawi go :/ tzn. tego MIN .. nie wiem jak to ugryźć

Dziwne.

Dokumentacja MySQL napisał(a)

MIN(expr), MAX(expr)
Returns the minimum or maximum value of expr. MIN() and MAX() may take a string argument; in such cases they return the minimum or maximum string value.

No to ja nie mam więcej pomysłów.

Jeżeli ma być na raz, to tylko stored proc. Innaczej się nie da. Trzeba pobrać min wartości dla count(*)>1 do bufora, a drugim zapytaniem je usunąć.

blm napisał(a)

Misiekd napisał(a)

a zobacz to http://www.xaprb.com/blog/2006/10/11/how-to-delete-duplicate-rows-with-sql/

No jest tam rozwiązanie tylko za pomocą kilku zapytań.. :/

co ty nie powiesz - a czytałeś to chociaż???

masz pierwszy lepszy przykład skopiowany na żywca - ile tu jest zapytań?

delete test_outer.*
from test as test_outer
where exists(
   select *
   from test as test_inner
   where test_inner.day = test_outer.day
   group by day
   having count(*) > 1
      and min(test_inner.id) <> test_outer.id
);

PS rotgut mam wątpliwości (ale nie testowałem) czy zadziała jeśli dubli do usunięcia będzie więcej niż 2

Misiekd napisał(a)

masz pierwszy lepszy przykład skopiowany na żywca - ile tu jest zapytań?

delete test_outer.*
from test as test_outer
where exists(
   select *
   from test as test_inner
   where test_inner.day = test_outer.day
   group by day
   having count(*) > 1
      and min(test_inner.id) <> test_outer.id
);

OK ok.. tu masz jedno zapytanie z podzapytaniami.. i tak jest OK... chociaż wywala mi błąd:

#1093 - You can't specify target table 'test_outer' for update in FROM clause 

Chodziło mi że nie może być coś takiego.. co rozwiązuje mój problem, ale to jest kilka osobnych zapytań:

CREATE TABLE nowe_pliki ...;

INSERT INTO nowe_pliki(rozszerzenie,rodzaj)
   SELECT DISTINCT rodzaj FROM pliki;

DROP TABLE pliki;

RENAME TABLE nowe_pliki pliki;

Zaczynam wątpić czy można ten problem załatwić jednym zapytaniem z podzapytaniami.. :/ ale wątpię, żeby prowadzący zajęcia ściemniał, bo kazał nam ten problem w ten sposób rozwiązać, aby uzyskać wyższą ocenę z koła :/
Problem jest przedstawiony tutaj..
http://www.xaprb.com/blog/2007/02/06/how-to-delete-duplicate-rows-with-sql-part-2/
sam już nie wiem co z tym fantem zrobić :/

blm napisał(a)

Problem jest przedstawiony tutaj..
http://www.xaprb.com/blog/2007/02/06/how-to-delete-duplicate-rows-with-sql-part-2/
sam już nie wiem co z tym fantem zrobić :/

W tym artykule, który przytaczasz jest napisane:

By reader request, this article explains ways to remove duplicate rows when they are completely identical, and you don’t have a primary key or other criterion to identify which rows to “save.”

no a przecież Twoja tabelka z pierwszego posta ewidentnie ma klucz główny. Jest nim pole rozszerzenie.
Z tego co zrozumiałem zadaniem nie jest usunięcie duplikatów w pełnym tego słowa znaczeniu tylko usunięcie wszystkich rekordów z powtarzającymi się wartościami w polu rodzaj (pozostawienie jednego rekordu każdego rodzaju). No chyba, że coś się zmieniło w specyfikacji zadania w międzyczasie? :-P

BTW.Moje rozwiązanie zakłada, że kluczem jest para (rozszerzenie,rodzaj) i na PostgreSQL działa (przynajmniej w moim rozumieniu zadania, które mozolnie objaśniałem powyżej).

@Misiekd: Skąd Twoje wątpliwości pochodzą?

@blm: Czy na pewno to musi być rozwiązanie pod MySQLa, czy może być SQL ogólnie? Bo wydaje mi się, że nieakceptowanie typu tekstowego przez MIN() to jest jakaś egzotyka MySQLa bardziej niż błąd.

rotgut napisał(a)

Z tego co zrozumiałem zadaniem nie jest usunięcie duplikatów w pełnym tego słowa znaczeniu tylko usunięcie wszystkich rekordów z powtarzającymi się wartościami w polu rodzaj (pozostawienie jednego rekordu każdego rodzaju).

Dokładnie tak jak piszesz :) ..

rotgut napisał(a)

Czy na pewno to musi być rozwiązanie pod MySQLa, czy może być SQL ogólnie? Bo wydaje mi się, że nieakceptowanie typu tekstowego przez MIN() to jest jakaś egzotyka MySQLa bardziej niż błąd.

Tutaj chyba masz racje z tym MySQL... się na niego uparłem i nie sprawdzałem w innym środowisku.. zainstaluje coś innego i pokombinuje dalej..

A więc zainstalowałem Microsoft SQL Server 2005 i wszystko gra :) . Oba zapytania są poprawne:
1:

DELETE p_a
FROM pliki AS p_a
WHERE EXISTS(
   SELECT *
   FROM pliki AS p_b
   WHERE p_b.rodzaj = p_a.rodzaj
   GROUP BY rodzaj
   HAVING count(*) > 1
      AND min(p_b.rozszerzenie) <> p_a.rozszerzenie
);

2:

DELETE p_a FROM pliki AS p_a WHERE rozszerzenie !=
    (SELECT MIN(rozszerzenie) FROM pliki p_b
     WHERE p_b.rodzaj = p_a.rodzaj GROUP BY rodzaj);

Dzięki za pomoc :)