Solved

Suppressing Duplicate Rows

Posted on 2008-06-18
5
1,136 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 74

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 74

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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

749 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