Oracle Query Question

[joero4]

I have a tricky sql question. What I am tring to do, is group some data in a table by a user_id and then grab the top 2 rows for each user_id which I grouped. Is that possible?

MY_TABLE
========================
User ID Data
1 23 <--
1 20 <--
1 10
2 23 <--
2 20 <--
2 40
3 20 <--
3 40 <--
4 10 <--

<-- are the rows I want to return. Any help is greatly appreciated. Thanks,
Joe

 

[SantaMufasa]

Joe,

Here is code, but I cheated...I used PL/SQL. If SQL-only is a restriction, then I yield to someone smarter than I.

create table my_table_top2 as select * from my_table where 1=2;
declare
	curr_id		number	:= -1;
	curr_cnt	number;
begin
	for r in (select * from my_table order by userid, data desc) loop
		if curr_id <> r.userid then
			curr_id		:= r.userid;
			curr_cnt	:= 0;
		end if;
		curr_cnt		:= curr_cnt+1;
		if curr_cnt <= 2 then
			insert into my_table_top2 values (r.UserID, r.Data);
		end if;
	end loop;
	commit;
end;
/
select * from my_table_top2 order by userid ,data desc;

Let me know if this is acceptable.

Dave
Sandy, Utah, USA @ 17:08 GMT, 10:08 Mountain Time

 

[karluk]

The following query should give what you want.

select userid, data from
(select userid,data,row_number()
over (partition by userid order by data desc) as row_num
from test_rank)
where row_num < 3
order by userid, data desc

 

[karluk]

In my previous post, you should substitute &quot;my_table&quot;, or whatever your table is called, for &quot;test_rank&quot;. That was just a temporary table I created to test the logic.