Solved

need to select top one in query

Posted on 2012-03-15
9
293 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 76

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
 

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
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.

 
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 76

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now