Solved

need to select top one in query

Posted on 2012-03-15
9
296 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
How our DevOps Teams Maximize Uptime

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

 

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 53

Expert Comment

by:Huseyin KAHRAMAN
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 53

Accepted Solution

by:
Huseyin KAHRAMAN 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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Suggested Solutions

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.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

710 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