Today a colleague of mine was trying to pick the top row in a result set on SQLServer, and we being Oracle Database fans could only think of ROWNUM. So here is what we found out after digging for some time.
Rownum is used in Oracle to retrieve the N-top records.
SELECT * FROM TABLENAME
WHERE ROWNUM<2
Note: Some things to keep in mind are that the “>=” or the “>” operators wont work with ROWNUM. Also remember ROWNUM is to only pick the required row from a result set and should not be confused with the position of the record in the table.
Finally the equivalent query in SQLServer would be
SELECT TOP 1 * FROM TABLENAME
and of course is more intuitive.
Your blog is interesting!
Keep up the good work!
How is this affected when you add an order by clause?
Hi Derek,
Well many a times people get confused with ROWNUM and ORDER BY clause. However each one servers their own purpose and needs to be understood correctly.
ROWNUM just gives you a pseudo column with a serial number.
ORDER BY will order your results.
Therefore when you add an ORDER BY clause you are first ordering you result set in some specific order or business logic. Then when you choose ROWNUM , you get the chosen record from this ordered result. This is best understood when you try is out with examples. Generally ORDER BY clause is used with ROWNUM to generate powerful queries.
Hope this helps,
Franklin
TOP 2 from DTL table for Each Master table…
RANK() Analytical + Corr Sub query
One can use below query with SQLSERVER…
select * from tbldtl_d a
where a.dtlid in (select dtlid from
(select dtlid,rank() over (order by mastid,dtlid desc) as rank from
tbldtl_d where a.mastid = mastid) c where c.rank < =3 )
They are not fully equivalent unless the only thing you want to do is get the first X records.
You could use rownum to say when rownum is greater than 1000 and less than 1200 and get only 200 records returned, rather than getting your table’s 300,000 rows returned and having to use middleware to output the 1000-1200 span for “page 5″ in web pagination.
My point is that the rownum function also allows ‘positioning WITHIN’ and TOP merely allows “positioning at the beginning”. Apparently. A weekend of googling and experimenting and failing everywhere has made me find the first thing about sql-server that I really dislike (this lack of its function). The hack workarounds suggested for many years online are crazy and mostly take way longer than the bloated query unless one has a million records.
PJ
Hi PJ,
Well i really dont know if you can see that is a built in property/functionality of rownum. However i know this can be implemented like this
select b.col
from(select a.*,rownum rnum
from(select * from test_tab a
where a.col like 'A%'
order by a.col ) a
where rownum=10
Now if this was an intended functionality, I would have loved it if it were as simple as
select * from test_tab where rownum >=10 and rownum<=20.
But it doesnt work like this. Hence I couldnt completely agree.
However Big Boss Oracle does support a between like approach with the help of ROWNUM
PS: I got this query from ASK Tom's website.