Re: MySQL Query Puzzle
| From: | Richard Quadling | Date: | Mon, 19 Jul 2010 14:01:16 +0000 |
| Subject: | Re: MySQL Query Puzzle | ||
| References: | 1 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-306998@lists.php.net to get a copy of this message | ||
On 19 July 2010 05:44, Peter <peters@egrabber.com> wrote:
> Hi All,
>
> I have a table which contain's some duplicate rows. I just want to delete
> the duplicate records alone
> not original records.
>
> Assume my table as look as below
>
> column1 column2
> 1
> a
> 1
> a
> 2
> b
> 3
> c
> 3
> c
>
>
>
> i want the above table need to be as below, After executing the mysql
> query.
>
> column1
> column2
> 1
> a
> 2
> b
> 3
> c
>
>
>
>
> Thanks in advance..
>
> Regards
> Peter
>
If your table had a db generated sequential unique identifier (an
identity / autoinc), then something along these lines may be what you
are looking for ...
-- Delete everything except the UniqueIDs we want to keep.
DELETE FROM
Table
WHERE
UniqueID NOT IN
(
-- Just get the UniqueIDs we want to keep.
SELECT
UniqueID
FROM
(
-- Get the earlist UniqueID for each Col1, Col2, pairing.
SELECT
Col1,
Col2,
MIN(UniqueID) AS UniqueID
FROM
Table
GROUP BY
Col1,
Col2
)
)
UNTESTED!!!!