Home » SQL & PL/SQL » SQL & PL/SQL » How to Display N row for each person ID? (10G, Open VMS)
How to Display N row for each person ID? [message #391657] Thu, 12 March 2009 19:59 Go to next message
niladri
Messages: 14
Registered: January 2009
Location: USA
Junior Member
Hi,

I have a (Inventory or Material Purchase) Table Structure like this:-

Temp_Inv_Turns_Buyer
(BUYER_CLS VARCHAR2(1) NOT NULL,
PN_CDE VARCHAR2(18) NOT NULL,
INV_MON01 NUMBER(12,2),
INV_MON02 NUMBER(12,2),
INV_MON03 NUMBER(12,2),
- - - - - - - - - - - -
- - - - - - - - - - - -
INV_MON11 NUMBER(12,2),
INV_MON12 NUMBER(12,2),
AVG_INV NUMBER(12,2),
COGS NUMBER(12,2),
INV_TURN_RT NUMBER(4,2))

How do I return (display) TOP 200 records (on AVG_INV) for each BUYER_CLS?
e.g. BUYER_CLS= 3, will show top 200 records on AVG_INV (Order By Average Inventory DESC). Next set will display for BUYER_CLS= 4, next BUYER_CLS= 5 and so on.
N.B. Order By AVG_INV DESC
Re: How to Display N row for each person ID? [message #391658 is a reply to message #391657] Thu, 12 March 2009 20:06 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

If you had the courtesy of actually reading the Posting Guidelines, you would have seen where you should SEARCH this forum before posting your FAQ.

It has been asked and answered here weekly.
SEARCH for "TOP N"
Re: How to Display N row for each person ID? [message #391659 is a reply to message #391657] Thu, 12 March 2009 20:23 Go to previous messageGo to next message
niladri
Messages: 14
Registered: January 2009
Location: USA
Junior Member
BlackSwan,

Did you really read my posting?? Not a simple ROWNUM <= 200 kind question. I recommend you to be 'courteous enough' before you post or reply! If you cannot read the whole post, don't reply

I am still looking for my answer, I will truly appreciate for anyone who helps.

Thank you in advance.

[Updated on: Thu, 12 March 2009 21:15]

Report message to a moderator

Re: How to Display N row for each person ID? [message #391661 is a reply to message #391657] Thu, 12 March 2009 21:56 Go to previous messageGo to next message
niladri
Messages: 14
Registered: January 2009
Location: USA
Junior Member
e.g. I am using an example how I want to return the result set:-

BUYER_CLS PN_CDE INV_MON01 INV_MON02.... AVG_INV
3 1-992-P 10.90 21.91 4000.92
3 2-737-S 30.09 42.90 3900.00
3 3-837-M 56.99 10.98 3880.00
-- --- --- --- -- --- -- --- --
-- --- --- --- -- --- -- --- --

(Display Top 200 Records for BUYER_CLS = 3)

4 3-837-D 62.76 71.87 6010.90
4 5-889-S 50.32 32.21 5092.31
-- --- --- --- -- --- -- --- --
-- --- --- --- -- --- -- --- --

(Display Top 200 Records for BUYER_CLS = 4)

5 7-633-H 12.98 98.09 4032.98
5 9-838-L 65.90 54.90 3098.90
-- --- --- --- -- --- -- --- --
-- --- --- --- -- --- -- --- --

(Display Top 200 Records for BUYER_CLS = 5)


Re: How to Display N row for each person ID? [message #391671 is a reply to message #391657] Thu, 12 March 2009 23:22 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hey pal,

Post the DDL and DML queries..and Format the desired results

Regards,
Ashoka BL
Re: How to Display N row for each person ID? [message #391681 is a reply to message #391659] Fri, 13 March 2009 00:59 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Actually, although I would word it differently, BlackSwan is right: this is a standard top-n query.
If you would have searched, you would probably have found different solutions then the rownum <= 200 you seem to expect.
Check out the analytical functions (DENSE_)RANK, ROW_NUMBER.
Previous Topic: help needed in Collection
Next Topic: need help in sql query
Goto Forum:
  


Current Time: Sun Dec 11 02:25:32 CST 2016

Total time taken to generate the page: 0.08027 seconds