Solved

Suppressing Duplicate Rows

Posted on 2008-06-18
5
1,107 Views
Last Modified: 2011-10-19
I want my query to only return distinct rows. It maybe easier to look at the attached which displays what i want the query output to be, rather than me desribe what i want the query to do!

Fig. 1 shows the raw data. Fig. 2 Shows the desired output.

As you can see from fig.2, where the 'Type', 'Color' and 'size' is repeated, i'd like the row to only be displayed once, but to also include a list of the owners in the same row. Is this possible? thanks


Sample.doc
0
Comment
Question by:tonMachine100
5 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 21815017
Use DISTINCT:

SELECT DISTINCT <columns>
FROM <tables>
<WHERE>
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21815263
go to asktom.oracle.com.  search for stragg (or search this site, it's here too a few times)

then


select type,color,size,stragg(owner) owner frmo your_table
group by type,color,size
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21815277
what version of oracle?  It helps to post to version zones in addition to the sql zone.  You'll get more coverage from volunteers and it gives us more information on what options to suggest.
0
 
LVL 22

Accepted Solution

by:
DrSQL earned 250 total points
ID: 21816376
tonMachine100,
   STRAGG might be overkill (but it's a consistently useful function, so I agree with ststuber that you should consider it).  You could also use a pivot approach if you know a maximum number of times that the duplicates might occur (this query would tell you that):

select max(occurances) from (
    select type,color,size,count(*) occurances from theSample
    group by type,color,size
/

To "pivot" the data you would (let's say the maximum you will support is 5 "duplicates"):

select type,color,size,
          rtrim(
          max(decode(owner_sequence, 1, owner)) ||','||
          max(decode(owner_sequence, 2, owner)) ||','||
          max(decode(owner_sequence, 3, owner)) ||','||
          max(decode(owner_sequence, 4, owner)) ||','||
          max(decode(owner_sequence, 5, owner)),',') owners
from
(select type,color,size,owner,
            row_number() over (partition by type,color,size order by owner) owner_sequence
   from theSample)
group by type,color,size
/

Good luck!

P.S.  this should work from 8.1.7 on up
0
 

Author Closing Comment

by:tonMachine100
ID: 31468487
That works a treat. Thank very much.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
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 gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

746 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

13 Experts available now in Live!

Get 1:1 Help Now