One quick suggestion…if you modify the query to this: SELECT COUNT(DISTINCT ip) AS IP_count, IP, query_string FROM myTable GROUP BY IP, query_string ORDER BY count DESC;
…then you should see each IP along with its count. FWIW, __Birm Ricardo Birmele, CISSP Senior Security Technologist Microsoft IT Security Operations • | •••• | • [Microsoft Logo] From: Petter von Dolwitz (Hem) [mailto:[email protected]] Sent: Thursday, 5 June, 2014 01:33 To: [email protected] Subject: Re: Help with query I tried to answer this earlier. Maybe the mail got suck somewhere. I'll try again. SELECT COUNT(DISTINCT ip) AS count, query_string FROM myTable GROUP BY query_string ORDER BY count DESC; This should give you one row per asked query. Rows are ordered by number of unique IPs that asked the query. Most popular first. Br, Petter 2014-05-30 1:29 GMT+02:00 Software Dev <[email protected]<mailto:[email protected]>>: We have a table with user entered queries, their IP. How could we write a query that will count and order queries by their count having a unique IP count > X. For example if we had the same IP enter the same query Y times we wouldnlt want to include this in the final result unless there have been X-Y other IP's that searched for that query. Is this perhaps better suited fro Pig? Thanks
