geeky

Oracle query top N results order by

Why is oracle query with any pagination flavor always so whacky?

Just wanted to return the top N results ordered by a field you have to do

select * from
(
select m.*,rank() over (order by m.msg_post_dt desc) rnk from board b, msg m
where b.id = m.board_id
and b.src_community = 'parents'
and b.board_id = 'toddlers_preschoolers'
and m.msg_content is not null
)
where rnk <= 5;

in MySQL:

select m.* from board b, msg m
where b.id = m.board_id
and b.src_community = 'parents'
and b.board_id = 'toddlers_preschoolers'
and m.msg_content is not null
order by m.msg_post_dt desc limit 5

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s