By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,686 Members | 2,392 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,686 IT Pros & Developers. It's quick & easy.

Selecting all records without dups based on one field?

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Jul 20 '05 #2

P: n/a
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.
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.