Remove rows with duplicate fields

misxa

Hello guyz! :-)

I have a problem, so I thought you might help me out:

I have a table which stores the user data, when accessing the website.

The table has the following fields:

-id, request, host, address, agent, host, referer...

The table has many occurencies of "address" field which are duplicated (but not the entire row).

So, the point is that I'm trying to eliminate rows with superfluous "address" fields.

I've tried with

CREATE TABLE temp AS SELECT DISTINCT * FROM table;

but it does not remove duplicates, as it should remove only the identical rows.

I've tried with

CREATE TABLE temp AS SELECT DISTINCT address FROM table;

It removes duplicates, but is copies only the "address" field. All other fields are truncated.

So, how should I remove the duplicates and leave only the first occurencies od the "address" field.

Thanks.

tomhath

I didn't test this, but you should be able to do it with a row aggregate function. Group by everything except address and pick the max or min of the address field, shouldn't matter which 😉

CREATE TABLE temp AS 
SELECT id, request, host, max(address), agent, host, referer... 
FROM table
group by id, request, host, agent, host, referer...

I don't think that will give you what you want however. Because you still must have a primary key (id?) or timestamp on the table (otherwise the select * would've worked). You'll probably have to select the max or min of some other columns as well as address.

misxa

Well, though your suggestions didn't solve my problem, you gave me the idea to explore aggregate functions, and after some research I came up with this:

CREATE TABLE temp SELECT * FROM table GROUP BY address;

This seems to eliminate every row in a table with duplicate 'address' field.

Thanks :-)

greg252

To select dup records, you can do a self join (check the man) which will return both records. Then, you can handle the duplication as you want- delete or whatever.