Solved

Suppressing Duplicate Rows

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL - Rotating Values in SQL 9 65
SQL Server syntax question 13 31
Sql server function help 15 30
Correct an issue with a where clause with calculation 2 30
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
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.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

770 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