0

we have a table like this

ip, class
192.168.1.1, 192.168.1
192.168.1.3, 192.168.1
12.34.56.78, 12.34.56
12.34.56.11, 12.34.56
12.34.56.16, 12.34.56
...
...

we need a query to select only X ips from the same class in a query. Eg, we need to select all the ips but max 2 ips from the same class.

We tried using temporary tables but we didn't had any luck. We need it to be able to do it from mysql only

mazgalici
  • 608
  • 6
  • 10
  • So you want to get the first distinct X IPs for a certain class? – SenorAmor Nov 25 '13 at 19:52
  • What about `SELECT DISTINCT(ip) FROM table WHERE class = 'whatever' LIMIT number` then? – SenorAmor Nov 25 '13 at 19:59
  • nope, sadly, is more complicated. We need all the ips, that will select just from a class. Thanks for trying – mazgalici Nov 25 '13 at 20:05
  • Ok, you're saying two different things. You told me what you needed, to which I gave you the answer. Now you're saying that's not what you need. Please update your answer with what your expected output is. – SenorAmor Nov 25 '13 at 20:08
  • Wait, are you trying to get the results from ALL classes? Try `SELECT DISTINCT(ip), class FROM table GROUP BY class LIMIT number` – SenorAmor Nov 25 '13 at 20:18
  • I think you will have to do something similar to [this question](http://stackoverflow.com/questions/532878/how-to-perform-grouped-ranking-in-mysql). In your case, the rank would be to keep only the values where rank < 2. – Filipe Silva Nov 25 '13 at 20:20

1 Answers1

1

unfortunately no easy way in mysql

select ip, class 
from (
    select ip, class,
          @num := if(@grp = class, @num + 1, 1) as row_number,
          @grp := class
    from mytable, (select @num := 0, @grp := '') temp_vars
    order by class) x
where row_number <= 2

This creates temporary table with emulated row_number per group and selects all rows with <= 2

Imre L
  • 6,159
  • 24
  • 32