Home » SQL & PL/SQL » SQL & PL/SQL » To get four top most records from a table (Oracle, 10G, windows XP)
To get four top most records from a table [message #402072] Thu, 07 May 2009 08:47 Go to next message
raghu.iv85
Messages: 13
Registered: December 2008
Location: banglore
Junior Member
Hi all,

I have one view named 'dshb_mcr_glob_we_time_v3'. In this view, I have 'perd_id' column. This column datatype is number. I want to get top four ids as a output. Can anyone please send me the query for that?

Thanks & Regards,
Raghu.
Re: To get four top most records from a table [message #402073 is a reply to message #402072] Thu, 07 May 2009 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Top N queries are a FAQ, please search

Regards
Michel
Re: To get four top most records from a table [message #402075 is a reply to message #402072] Thu, 07 May 2009 08:52 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
http://www.lmgtfy.com/?q=oracle+TOP%20N


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: To get four top most records from a table [message #402168 is a reply to message #402072] Thu, 07 May 2009 23:37 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Analytic function row_number() would solve your problem.

regards,
Delna
Re: To get four top most records from a table [message #402174 is a reply to message #402072] Fri, 08 May 2009 00:03 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
Please post DDL, DML, & SQL as proof
Re: To get four top most records from a table [message #402177 is a reply to message #402072] Fri, 08 May 2009 00:12 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
I think below query can be used.
I have not tested it.

SELECT   *
FROM (SELECT perd_id, ROW_NUMBER () OVER (ORDER BY perd_id DESC) rn
      FROM dshb_mcr_glob_we_time_v3)
WHERE rn <= 3
ORDER BY perd_id DESC



Thanks,
Re: To get four top most records from a table [message #402192 is a reply to message #402177] Fri, 08 May 2009 01:02 Go to previous message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With rn<=3 I doubt you get 4 rows.

Once again, your post don't add anything to previous ones but errors.
First give clue (but this one has already been given)
Then, if OP shows what he tried and couldn't get it, post a solution, a tested solution, a working solution.
But do not be rude agains others ignoring their posts.

Regards
Michel
Previous Topic: Data is set has Column name during Union Query
Next Topic: Deleting records from a table which is having 60 million records
Goto Forum:
  


Current Time: Wed Dec 07 14:18:30 CST 2016

Total time taken to generate the page: 0.13099 seconds