How to remove duplicates in a subquery using distinct in oracle

Question

I want to remove duplicate rows returned from this sql:

Select
      *
    From (Select
      Security_Symbol,
      Security_Name,
      date1,
      time1,
      last1,
      Changefromopen,
      percentchangefromopen
    From
      xgdv
    Where
      Security_Symbol In ('abc', 'def', 'ghi')
    Order By
      date1 desc,
      time1 asc) where rownum <= 3

Using a distinct in subquery doesn't remove duplicate records.

What's wrong with using distinct in subquery. How do I achieve this?

Do you expect all of the fields to be unique? Or are you considering a duplicate two rows with equivalent Security_Symbol and Security_Name as the same row? — Aiias
– Aiias, Commented Mar 30, 2013 at 7:16
yes, later one. subquery should return unique Security_Symbol. — Gaurav Sharma
– Gaurav Sharma, Commented Mar 30, 2013 at 7:20
can you post sample records or a screenshot of your duplicate records. — John Woo
– John Woo, Commented Mar 30, 2013 at 7:34

Thomas Mueller · Accepted Answer · 2013-03-30 09:09:09Z

1

If you have multiple rows with the same Security_Symbol/Security_Name, but only want one row for each combination in the result, then you need to decide what data you want. You may want the range of the other columns, as in:

Select
  Security_Symbol,
  Security_Name,
  min(date1), max(date1),
  min(time1), max(time1)
  min(last1), max(last1),
  min(Changefromopen), max(Changefromopen),
  min(percentchangefromopen), max(percentchangefromopen)
From
  xgdv
Where
  Security_Symbol In ('abc', 'def', 'ghi')
Group by
  Security_Symbol, Security_Name
Order By
  Security_Symbol, Security_Name

This may not be the solution you are looking for; if it's not, please specify what data of the other columns you are interested in. For example: any random row, the average,...

answered Mar 30, 2013 at 9:09

Thomas Mueller

50.4k16 gold badges123 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user2207949 · Accepted Answer · 2013-03-31 19:50:06Z

0

try calling all columns from the base table in the subquery - it might be more obvious which rows are similar. you could also try adding a rank to the subquery to highlight the duplicate rows. this would be a starting point to work out what is causing the dupes.

answered Mar 31, 2013 at 19:50

user2207949

112 bronze badges

Collectives™ on Stack Overflow

How to remove duplicates in a subquery using distinct in oracle

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related