Equivelant of MySQL Limit in Oracle.Query includes several tables?

I have the following query in MySQL and want to convert it to Oracle. I tried row_number() function and subqueries in Oracle but could not make it run properly. The query is kinda weird and covers multiple tables.

Original MySQL Query:

select DISTINCT uc.virtual_clip_id, uc.clip_id, uc.duration, uc.title,  
                uc.thumbnail,uc.filename, uc.description, uc.block_id_start, 
    开发者_如何学C            uc.block_id_end, u.uname,uc.cdate, uc.ctime, uc.privacy_level, uc.user_id 
FROM
    user_clips uc, users u, user_like ul

WHERE 
     ul.user_id="+user_id+" and u.user_id=uc.user_id and  
     uc.virtual_clip_id=ul.virtual_clip_id and ul.like_status='1'
ORDER by 
      virtual_clip_id DESC
LIMIT "+offset+",4"

I could not find the proper statement to replace LIMIT so make the query run against Oracle.

Thanks.

It's a bit of a pain but you can use rownum

select * from
(
select rownum as row_number, base_data.* from
(
select DISTINCT uc.virtual_clip_id, uc.clip_id, uc.duration, uc.title,
 uc.thumbnail,uc.filename, uc.description, uc.block_id_start, uc.block_id_end, u.uname,uc.cdate, uc.ctime, uc.privacy_level, uc.user_id FROM user_clips uc, users u, user_like ul

WHERE ul.user_id="+user_id+" and u.user_id=uc.user_id and
 uc.virtual_clip_id=ul.virtual_clip_id and ul.like_status='1' 
) base_data
ORDER by virtual_clip_id DESC
) sorted
WHERE row_number <= X

The only reason I subselected twice was the distinct is performing a group and I kept the ordering outside of it, it could be done with a single subselect.

select * from (
select DISTINCT
    uc.virtual_clip_id
  , uc.clip_id
  , uc.duration
  , uc.title
  , uc.thumbnail
  , uc.filename
  , uc.description
  , uc.block_id_start
  , uc.block_id_end
  , u.uname
  , uc.cdate
  , uc.ctime
  , uc.privacy_level
  , uc.user_id
  , row_number() over(order by uc.virtual_clip_id DESC) row_nr
   FROM
    user_clips uc
  , users u
  , user_like ul
  WHERE
    ul.user_id         = "+user_id+"
and u.user_id          = uc.user_id
and uc.virtual_clip_id = ul.virtual_clip_id
and ul.like_status     = '1'
)
 where row_nr < 5 ;

select * 
from 
 (
  select DISTINCT uc.virtual_clip_id, uc.clip_id, uc.duration, uc.title,  
                  uc.thumbnail,uc.filename, uc.description, uc.block_id_start, 
                  uc.block_id_end, u.uname,uc.cdate, uc.ctime, uc.privacy_level, uc.user_id 
                , row_number() over(order by uc.virtual_clip_id desc) rn
  FROM
      user_clips uc, users u, user_like ul
  WHERE 
       ul.user_id="+user_id+" and u.user_id=uc.user_id and  
       uc.virtual_clip_id=ul.virtual_clip_id and ul.like_status='1'
 )
where rn between "+offset+" and 4