OraFAQ Forum: SQL & PL/SQL » last ten entries

Home » SQL & PL/SQL » SQL & PL/SQL » last ten entries

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

last ten entries [message #20691]

Fri, 14 June 2002 03:17

Tod
Messages: 6
Registered: June 2002

Junior Member

Hi everybody,

I have to write a SQL statement which selects the last ten entries (ordered by the date). I tried really hard, but I can't get a result! how can I do this?

Thanks for help,
Tod

Report message to a moderator

Re: last ten entries [message #20692 is a reply to message #20691]

Fri, 14 June 2002 03:44

vipin
Messages: 40
Registered: May 2002

Member

use rownum <= 10 in your where clause

Report message to a moderator

Re: last ten entries [message #20702 is a reply to message #20691]

Fri, 14 June 2002 08:35

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

select *
  from (select *
          from t
         order by date_entered desc)
 where rownum <= 10;

Report message to a moderator

Re: last ten entries [message #20708 is a reply to message #20691]

Fri, 14 June 2002 12:55

sridhar
Messages: 119
Registered: December 2001

Senior Member

Todd Barry,
Do you think Tod is asking for "last 10 entries"
based on "date_entered" and present them in "order".
If he is, wouldn't this be a double sort?

SELECT * FROM
(SELECT * FROM
(SELECT * FROM table_name
ORDER BY DATE_ENTERED DESC)
WHERE ROWNUM <= 10)
ORDER BY DATE_ENTERED;

I may be wrong, but that is how the spec reads to me.
Thx,
SriDHAR

Report message to a moderator

Re: last ten entries [message #20710 is a reply to message #20708]

Fri, 14 June 2002 13:54

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

I couldn't tell from the spec whether the ten dates should be in ascending or descending order. Usually though, when people ask for the "last" ten, they like to see them in descending order, but if Tod wants them in ascending order, he can add the order clause as you've shown.

Report message to a moderator

Re: last ten entries [message #20712 is a reply to message #20691]

Fri, 14 June 2002 18:34

Su
Messages: 154
Registered: April 2002

Senior Member

Since Tod's query is not clear and I am not sure whether you all guys thinking in right direction, here is another from my side assuming that tod's query is to display the last ten order by date in descending (where the recent comes first) for each item. For example to list the employees those who joined recently departmentwise and listing first who joined most recently in each department.

SELECT ENO,ENAM,DEPT,JOINDATE, DENSE_RANK() OVER (PARTITION BY DEPT ORDER BY JOINDATE DESC) RN FROM EMPLOYEE WHERE RN<=10;

Hope my assumption is right and this helps you to reslve your query.

Good luck :)

Report message to a moderator

Re: last ten entries [message #20726 is a reply to message #20712]

Sat, 15 June 2002 17:39

sridhar
Messages: 119
Registered: December 2001

Senior Member

Su/Todd Barry,

Su: Your solution def works, no doubt about it.

As we are on the subject here,
I have tested the performance of analytical functions with alternative SQLs and on small tables analytical queries are okay, but on big tables they did not prove well in comparision with alternative SQLs.

Can you guys share your experience on AF(analytical functions)?

Thx,
Sri

Report message to a moderator

Previous Topic:	Re: Extract numbers from a string
Next Topic:	executing a string in oracle......

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 10:27:03 CDT 2024