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.
HY GUYS I WANT A QUERY THAT TO GET A NUMBER OF ROWS BETWEEN 500 AND 1000 IN ANY TABLE CAN ANY ONE HELP ME WITH EXAMPLE
Well, I have been using a query from “Ask Tom” website. I believe this is what you are looking for
select *
from ( select a.*, rownum rnum
from ( YOUR_QUERY_GOES_HERE -- including the order by ) a
where rownum = MIN_ROWS
/
Try it out. that’ll do it. It will *not* work in 8.0 or before.
Here is the link
http://asktom.oracle.com/pls/asktom/f?p=100:11:3842718368184976::::P11_QUESTION_ID:127412348064
Hey nice read, but what if i need to select the third row out of 10 rows table? can i do it using Top N funtion? or alternate we have….
I havent tried this code, but just from the top of my head. Try something like this.
1)First get the Top 3 rows
2)Order them with the 3rd row at the top of the list / first in the list
3)Get the Top 1 row from this previous list
SELECT TOP 1 * FROM (SELECT TOP 3 * FROM TABLENAME order by columname desc)
Hope it works!!
Regards,
Franklin
Thanks Franklin…. i did try that logic but it didn’t work for me… let me give you my explanantion here… i am trying to access some data from the third two of my table1, and table1 has join with other table in the same query and the information would just be pop up in one col…
I still didnt quite get your problem. Could you explain with a sample data set and the output you are expecting?
Regards,
Franklin
Thanks for asking Franklin,
okay, let me try here…
i have a master table and a detail table and i am joining them together…
Master Table
MasterID / Salesperson ID / TeamID / Current Detail ID
1 | 105 | 10 | 3
2 | 102 | 10 | 4
3 | 103 | 12 | 6
Detail Table
DetailID / Master ID / SalespersonID / TeamID / DateCreated
1 | 1 | 101 | 10 | 08 Apr 10
2 | 1 | 104 | 12 | 08 Apr 12
3 | 1 | 105 | 10 | 10 Apr 12
4 | 2 | 103 | 12 | 10 Apr 12
now. master and detail tables are linked and the relationship is one to many….. right now Master ID 1 is connected to Detail ID 3 which is fine, i want to get Detail ID 2 data, which is a part of history data but….. Hope that answer the question.
thanks again for looking into this.
I was not able to get hold an SQL Server to try this out, but its kind of what I was thinking. Could you try it out and let me know if it works
select Top 1 from
(
select Top 2 from
(
select b.MasterID,b.DetailId,b.DateCreated
from MASTER_TABLE a, DETAIL_TABLE b
where a.MasterID=b.MasterID
and a.MasterID = 1
order by b.DateCreated desc
) c
order by c.DateCreated asc
)
Regards,
Franklin
Hi Franklin, thanks again for taking your time out and help me 🙂
though it is really a useful query but can we avoid the harding of Master ID? ( and a.MasterID=1)?
actually i am trying to get the second last row from the Detail table or the row that was previous linked with Master ID.
Thansk
I really didnt understand if the query worked or not? Also if you do not need the a.MasterID you can just remove it and you will get the result for all the MasterIds.
Hi Frank,
Thanks Again for all this.
i tried using this query but it didn’t work out there, so created a view and function to achive the task…..
Again, Thanks a lot for helping me..
Take Care