Solved

Suppressing Duplicate Rows

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

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

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

630 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