Home » SQL & PL/SQL » SQL & PL/SQL » query how to limit records output
query how to limit records output [message #348147] Mon, 15 September 2008 15:36 Go to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
I have a query that is producing output as follows


Account1, Subscriber1, xxxx, yyyy, zbzd
Account1, Subscriber2, xxxx, yyyy, zbsf
Account2, Subscriber1, xxxx, yyyy, zssg
Account3, Subscriber1, xxxx, yyyy, zsza
Account3, Subscriber2, xxxx, yyyy, zzzz
.
.
.


I would like to limit one record per account to where I have


Account1, Subscriber1, xxxx, yyyy, zbzd
Account2, Subscriber1, xxxx, yyyy, zssg
Account3, Subscriber2, xxxx, yyyy, zzzz

Is there a clause I can use to do this in sql ?
Re: query how to limit records output [message #348148 is a reply to message #348147] Mon, 15 September 2008 15:39 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

>Is there a clause I can use to do this in sql ?
Yes, but what is the criteria for deciding which row is returned?
Re: query how to limit records output [message #348149 is a reply to message #348147] Mon, 15 September 2008 15:42 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
And before you say the first row please define what that means as there is no FIRST or LAST in a relational database unless clearly defined.
Re: query how to limit records output [message #348151 is a reply to message #348148] Mon, 15 September 2008 15:48 Go to previous messageGo to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
the criteria is :

Select Account,Subscriber,location,type,servicecode

the problem is that one account can have multiple subscribers but I only care about one record from any account.
Re: query how to limit records output [message #348152 is a reply to message #348151] Mon, 15 September 2008 16:24 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
OK, but you still haven't answered the questions asked of you.
Re: query how to limit records output [message #348154 is a reply to message #348152] Mon, 15 September 2008 16:52 Go to previous messageGo to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
the criteria is :

Select Account,Subscriber,location,type,servicecode
from AccountInfoTable
where location='xxxx'
and type='yyyy'


what makes each record unique is Account,Subscriber combination.
however in my query I just want one account record.

I am working on Oracle9i Enterprise Edition Release 9.2.0.7.0

If this does not answer your question please rephrase.

[Updated on: Mon, 15 September 2008 17:00]

Report message to a moderator

Re: query how to limit records output [message #348156 is a reply to message #348147] Mon, 15 September 2008 17:02 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

rows in a table are like balls in a basket.
If there are 3 RED balls in the basket & you are supposed to SELECT a red ball, how do you decide which ball to grab?

How does Oracle decide which of multiple records actually get returned?

[Updated on: Mon, 15 September 2008 17:05] by Moderator

Report message to a moderator

Re: query how to limit records output [message #348159 is a reply to message #348156] Mon, 15 September 2008 17:17 Go to previous messageGo to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
Well my query would be simple if I was returning one column, then I would use the distinct function.

I was also thinking in term of the rownum psudo column but that would still return multiple columns.

So to answer you question, I would
select red ball where rownum < 2

Confused

[Updated on: Mon, 15 September 2008 17:39]

Report message to a moderator

Re: query how to limit records output [message #348201 is a reply to message #348159] Tue, 16 September 2008 00:29 Go to previous message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
To cut a long story short:
Quote:
one account can have multiple subscribers but I only care about one record from any account.


It appears that you don't actually care which subscriber you'll get, so one solution might look like this:
select account, min(subscriber) subscriber, location, type, servicecode
from this_table
group by account, location, type, servicecode
order by account, location, type, servicecode
Previous Topic: different results
Next Topic: Comma Seperated String (merged)
Goto Forum:
  


Current Time: Mon Dec 05 18:51:17 CST 2016

Total time taken to generate the page: 0.23897 seconds