need to select top one in query

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 .....
sl1ngerAsked:
Who is Participating?
 
HainKurtSr. System AnalystCommented:
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
 
micropc1Commented:
Try...

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

Open in new window

0
 
sl1ngerAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slightwv (䄆 Netminder) Commented:
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
 
sl1ngerAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
sl1ngerAuthor Commented:
yes, they already have unique parentid.  I want to remove the row shown out, ideally leaving one row per each masterid.
0
 
sl1ngerAuthor Commented:
slightwv - the error wanted me to include order in the syntax

I'll do a search for your way
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.