Sub SELECT?

[steve99999]

Hi,

I have hot a table with 50,000 + rows of IP addresses ripped from apache logs. I need to SELECT DISTINCT the ip and check if the field 'form' is set to 1. if form is not =1 then still select eg.

TABLE -
IP form
169.168.0.1 0
169.168.0.1 0
169.168.0.1 1
169.168.0.1 0
194.25.25.25 0
45.54.26.254 0
45.54.26.254 1

Required Results-

169.168.0.1 1
194.25.25.25 0
45.54.26.254 1

I have managed to get these results doing a select distinct on the ip then dumping the results in to an array. Then doing a select on the ip from the array with where form=1 but it is taking 4-5 minutes.

Is there a faster way?

Many thanks

 

[r937]

select ip,max(form) from yourtable group by ip

rudy

http://r937.com/

SQL Consulting

 

[steve99999]

Cheers Rudy !!!

It works a treat and takes only 8 seconds

Here is my version:

SELECT id,ip,max(form) as form1 from jan2004 GROUP BY ip ORDER BY date

Thanks again !!!

Steve

 

[r937]

GROUP BY id, ip

rudy

http://r937.com/

SQL Consulting