Home » SQL & PL/SQL » SQL & PL/SQL » Pick records from different group. (Oracle 10gR2)
Pick records from different group. [message #309470] Thu, 27 March 2008 11:00 Go to next message
ramya.pathak
Messages: 15
Registered: August 2007
Junior Member
Hi,
I have a requirement in which I have to pick account numbers from a table based on different source codes.
Account numbers shud be picked so as NO source codes are left out.
Also the number of acc numbers picked from a particular code is fixed. i.e. Pick a acc no from each GROUP of source code separately sequentially untill the total of 200 account numbers are reached. This 200 can vary.
I hope am clear. I have written 2 queries for this. 2nd one is very slow.
Just wanted to know whether this can be converted into one single query.. Or atleast we can optimize the 2nd query.

Its kinda urgent. Thanks for the help..
  • Attachment: Query.txt
    (Size: 1.97KB, Downloaded 157 times)
Re: Pick records from different group. [message #309480 is a reply to message #309470] Thu, 27 March 2008 12:15 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since you did not provide any info about the structure of your tables, I'll have to guess here.

- I GUESS tran_date is a varchar2 field, in which dates are stored as YYYYMMDD
If so, why the to_date? If you have an index on tran_date, the to_date will render it useless
- rownum is not a string, but a number.
- where do these rownum < x from? Seems very weird to pick some random records from the table

Oh, and we really, really do not care whether or not it's urgent.

[Updated on: Thu, 27 March 2008 12:15]

Report message to a moderator

Re: Pick records from different group. [message #309592 is a reply to message #309480] Fri, 28 March 2008 01:34 Go to previous message
ramya.pathak
Messages: 15
Registered: August 2007
Junior Member
Hi Frank,
Thanks for the useless reply.
Yes tran_date is a varchar2 field. But there are no indexes on it.
And I know rownum is a number. And have changed that. But neways it worked.
The records are picked in a fixed number from the table corresponding to each source code. Hope am clear this time.
Thanks
Previous Topic: Merging Tables through SQL
Next Topic: sql DataType
Goto Forum:
  


Current Time: Fri Dec 09 00:08:21 CST 2016

Total time taken to generate the page: 0.17932 seconds