Solved

need to select top one in query

Posted on 2012-03-15
9
298 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

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 56

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 56

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup
Suggested Courses

623 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