Top 2 records 2

[quisar]

Hi
I was using sql server 2000, where it is easy to get top n records by writing select top 2 * from emp. How is the same in oracle.
Also select rowid from emp does not give any results. Why is that so?

Quisar

 

[blom0344]

Try:

SELECT * FROM TABLE WHERE ROWNUM <3,
Rowid and rownum are pseudo columns, but for some (?) reason Rownum does show and Rowid not.......... T. Blom
Information analyst
tbl@shimano-eu.com

 

[quisar]

Thanks, but this is not what I want.

I have a date col in the table. I want the latest record entered. I record the date and time when the rec was inserted and want the last 2 records of the latest date. I hope you understand. Let me know if any clarification req.

Thanks

Quisar

 

[blom0344]

Hello Quisar,

My answer was on your generic first question how to select the top 2 rows from a table. Your answer describes something quite different. Where do you store the recorded date and time? In the table itself as a timestamp field? If so, you could first do an order by on the timestamp field (can be expensive) and then get the top 2 rows. Using things like ROWNUM work of course only with sorted data. T. Blom
Information analyst
tbl@shimano-eu.com

 

[sem]

select * from
(select * from <your tab> order by <date col>)
where rownum <3

 

[jee]

Let me just throw in some advice here that may or may not apply to your case. The solution SEM provides works fine, but if you have a ton of data the response may not be what you want. There is an alternate way to do top-N queries in Oracle 8i - using analytic functions. These functions were designed for the data warehousing enviroment where you have lots of data. I had a case where I was doing a top-10 query that just took forever with SEM's solution (don't know how long, I never was patient enough to let it complete, but I let it go 20 minutes). When I changed over to using an analytic function query, it took 23 seconds.

One limitation with the analytic functions is that they are not supported within PL/SQL in 8i (they are supported in 9i), so if you want to use them within PL/SQL you have to use dynamic SQL.

Here is an example of such a top-10 query (retrieves the 10 most recently installed seals on all doors).

SELECT door_id, seal_number, rank
  FROM (
     SELECT door_id, seal_number, 
            rank() over (PARTITION BY door_id
            ORDER BY event_date DESC) rank
       FROM seal_event
      WHERE status = 'INSTALLED')
 WHERE rank <= 10
 ORDER BY door_id, rank

 

[quisar]

Thanks sem and T. Blom. This is what I wanted.

Quisar