How to limit the number of rows for a field value in SQL?

Question

For example, I have a table like this in Hive:

1 1 1 4 1 8 2 1 2 5 3 1 3 2 

and I only want to return the first two rows of each unique value of the first column. I want this to limit the amount of data that I transfer from Hive to MySQL for reporting purposes. I would like one HiveQL query to give me the following:

 1 1 1 4 2 1 2 5 3 1 3 2 

Answer 1

Unfortunately, mysql has no analytic functions. So you have to play with variables. Suppose you have an auto-increment field:

 mysql> create table mytab ( -> id int not null auto_increment primary key, -> first_column int, -> second_column int -> ) engine = myisam; Query OK, 0 rows affected (0.05 sec) mysql> insert into mytab (first_column,second_column) -> values -> (1,1),(1,4),(2,10),(3,4),(1,4),(2,5),(1,6); Query OK, 7 rows affected (0.00 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> select * from mytab order by id; +----+--------------+---------------+ | id | first_column | second_column | +----+--------------+---------------+ | 1 | 1 | 1 | | 2 | 1 | 4 | | 3 | 2 | 10 | | 4 | 3 | 4 | | 5 | 1 | 4 | | 6 | 2 | 5 | | 7 | 1 | 6 | +----+--------------+---------------+ 7 rows in set (0.00 sec) mysql> select -> id, -> first_column, -> second_column, -> row_num -> from ( -> select *, -> @num := if(@first_column = first_column, @num:= @num + 1, 1) as row_num, -> @first_column:=first_column as c -> from mytab order by first_column,id) as t,(select @first_column:='',@num: =0) as r; +----+--------------+---------------+---------+ | id | first_column | second_column | row_num | +----+--------------+---------------+---------+ | 1 | 1 | 1 | 1 | | 2 | 1 | 4 | 2 | | 5 | 1 | 4 | 3 | | 7 | 1 | 6 | 4 | | 3 | 2 | 10 | 1 | | 6 | 2 | 5 | 2 | | 4 | 3 | 4 | 1 | +----+--------------+---------------+---------+ 7 rows in set (0.00 sec) mysql> select -> id, -> first_column, -> second_column, -> row_num -> from ( -> select *, -> @num := if(@first_column = first_column, @num:= @num + 1, 1) as row_num, -> @first_column:=first_column as c -> from mytab order by first_column,id) as t,(select @first_column:='',@num: =0) as r -> having row_num<=2; +----+--------------+---------------+---------+ | id | first_column | second_column | row_num | +----+--------------+---------------+---------+ | 1 | 1 | 1 | 1 | | 2 | 1 | 4 | 2 | | 3 | 2 | 10 | 1 | | 6 | 2 | 5 | 2 | | 4 | 3 | 4 | 1 | +----+--------------+---------------+---------+ 5 rows in set (0.02 sec) 

Answer 2

Hive solution would be

 SELECT S.col1, S.col2 FROM (SELECT col1, col2, row_number() over (partition by col1) as r FROM mytable) S WHERE Sr < 3