Solved

Suppressing Duplicate Rows

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

751 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