Selecting all records without dups based on one field?

[Zaphod]

I have a table with 5 fields, of which several sometimes have
duplicates.

example;

| id | item | day | hour | ip_address |
| 1 | 3 | 2 | 11 | 204.156.33.78 |
| 2 | 7 | 2 | 15 | 122.165.177.211 |
| 3 | 1 | 3 | 1 | 205.52.79.122 |
| 4 | 7 | 3 | 9 | 122.165.177.211 |
| 5 | 11 | 3 | 9 | 177.15.99.111 |
| 6 | 24 | 3 | 17 | 122.165.177.211 |
As you can see the ip address may obviously have dupe entries (as do
other fields but I don't care if they are dupes) and I want to query
the table and get all the fields but only one where there is dupe
ip_address 's.

So that I would get these results;

| id | item | day | hour | ip_address |
| 1 | 3 | 2 | 11 | 204.156.33.78 |
| 2 | 7 | 2 | 15 | 122.165.177.211 |
| 3 | 1 | 3 | 1 | 205.52.79.122 |
| 5 | 11 | 3 | 9 | 177.15.99.111 |
Thank you in advance for your assistance.
Z

[usenet]

On 30 Aug 2004 07:40:46 -0700, za************@truckload2000.com (Zaphod) wrote:

As you can see the ip address may obviously have dupe entries (as do
other fields but I don't care if they are dupes) and I want to query
the table and get all the fields but only one where there is dupe
ip_address 's.

Check out SELECT DISTINCT

[Bill Karwin]

Zaphod wrote:

As you can see the ip address may obviously have dupe entries (as do
other fields but I don't care if they are dupes) and I want to query
the table and get all the fields but only one where there is dupe
ip_address 's.

So that I would get these results;

| id | item | day | hour | ip_address |
| 1 | 3 | 2 | 11 | 204.156.33.78 |
| 2 | 7 | 2 | 15 | 122.165.177.211 |
| 3 | 1 | 3 | 1 | 205.52.79.122 |
| 5 | 11 | 3 | 9 | 177.15.99.111 |

SELECT DISTINCT won't help in this case. DISTINCT applies to all
columns in the select-list, not just one column.

You need something like this:

select t.id, t.item, t.day, t.hour, t.ip_address
from myTable t
where id = (
select min(id)
from myTable t2
where t2.ip_address = t.ip_address
);

This is called a correlated subquery, FWIW.

Another variation, non-correlated this time:

select t.id, t.item, t.day, t.hour, t.ip_address
from myTable t
where id in (
select min(t2.id)
from myTable t2
group by t2.ip_address
);

These subqueries are not supported prior to MySQL 4.1.

If you absolutely must get this working in MySQL 4.0 or earlier, then
you probably have to do it in two queries:

select min(id) from myTable group by ip_address

and join the results of this query together in a comma-separated list:
"1,2,3,5". You do this in your application code. For instance, in
Perl/DBI:
my $ids = $dbh->selectcol_arrayref($sql);
my $list_of_ids = join(',', @$ids);

Then substitute your comma-separated list into the following query:

select t.id, t.item, t.day, t.hour, t.ip_address
from myTable t
where id in ( $list_of_ids );

Be careful of the case where $list_of_ids is an empty string, because
it'll be a syntax error for the IN predicate.

Regards,
Bill K.