Solved

need to select top one in query

Posted on 2012-03-15
9
295 Views
Last Modified: 2012-03-15
I have a query that  returns mutliple rows under certain circumstances, but I don't want it to do that.  I'm trying to get only one from within the select.
I need help with the syntax.

SELECT DISTINCT
           'AB' AS appid,
            mft.rep,
            top 1 (mft.atd_unique_cust_key AS parentid)     --- this is where I need the syntax help
from .....
0
Comment
Question by:sl1nger
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 7

Expert Comment

by:micropc1
ID: 37725511
Try...

SELECT DISTINCT
           'AB' AS appid,
            mft.rep,
            mft.atd_unique_cust_key AS parentid    ---
FROM
...
WHERE ROWNUM <= 1

Open in new window

0
 

Author Comment

by:sl1nger
ID: 37725597
I was trying not to complicate the query, but it looks more like...

SELECT DISTINCT
           'AB' AS appid,
            mft.rep,
            mft.atd_unique_cust_key AS parentid, ---still think I need  here
            app.masterid,
            app.serviceid .....xml already here to make it look like results

from .....

...
so, the results currently look like...

appid       rep      parentid     masterid    serviceid
AB           me       1000AQ     1005           101, 301
AB           me       100BAQ     1005           101, 301
AB           me       100CCQ      1235           101


but, need it to look like this...
appid       rep      parentid     masterid    serviceid
AB           me       1000AQ     1005           101, 301
AB           me       100CCQ      1235           101
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37725798
Try something like this (typed in, untested):

Select appid, rep, parentid, masterid, serviced
From
(
SELECT DISTINCT
           'AB' AS appid,
            mft.rep,
            mft.atd_unique_cust_key AS parentid, ---still think I need  here
            app.masterid,
            app.serviceid .....xml already here to make it look like results,
row_number() over(partition by appid, Rep, masterid) myrownum
from .....
)
Where myrownum=1;
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

Author Comment

by:sl1nger
ID: 37725939
slightwv - that only brought back one record

Also, it would not accept the partition by... I used order by and still just got 1

so, I'm getting ....

appid       rep      parentid     masterid    serviceid
AB           me       1000AQ     1005           101, 301


when I need to get..
appid       rep      parentid     masterid    serviceid
AB           me       1000AQ     1005           101, 301
AB           me       100CCQ      1235           101
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 37726173
you are saying you want unique parentid, but in your current result it is already unique!

appid       rep      parentid     masterid    serviceid
AB           me       1000AQ     1005           101, 301
AB           me       100BAQ     1005           101, 301
AB           me       100CCQ      1235           101

but, need it to look like this...
appid       rep      parentid     masterid    serviceid
AB           me       1000AQ     1005           101, 301
AB           me       100CCQ      1235           101

why you filter 100BAQ out? do you mean you want unique masterid?
what is your current query, what is our sample data, and what is the desired result?
your posts are confusing...
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 total points
ID: 37726253
>>Also, it would not accept the partition by... I used order by and still just got 1

The partition by is what makes it work.  It is basically a group by but inline.  Since you changed the syntax, you changed the behavior.  Without the grouping, it should only return 1 row.

What was the error?  I'm away from a real computer but it is likely a simple syntax error.

Check the online docs for the syntax.  There are also a ton of examples on the web.
0
 

Author Comment

by:sl1nger
ID: 37726260
yes, they already have unique parentid.  I want to remove the row shown out, ideally leaving one row per each masterid.
0
 

Author Comment

by:sl1nger
ID: 37726267
slightwv - the error wanted me to include order in the syntax

I'll do a search for your way
0
 
LVL 51

Accepted Solution

by:
HainKurt earned 300 total points
ID: 37726285
use this syntax...

select * from (
  select ..., row_number() over (partition by masterid order by atd_unique_cust_key) rn
  from ...
  where ...
) x where rn=1
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question