Home » SQL & PL/SQL » SQL & PL/SQL » How to avoid duplicate rows from the result of a query ? (oracle 9i,windows)
How to avoid duplicate rows from the result of a query ? [message #343132] Tue, 26 August 2008 06:59 Go to next message
kuldip
Messages: 10
Registered: November 2007
Junior Member
How to avoid duplicate rows from the result of a joined query ?

I wrote a query as

SELECT T.TARGET_NAME,M.METRIC_NAME
FROM (select target_name,target_type,target_guid
from mgmt_targets
where target_type = 'host'
and target_name like '%oracleoutsourcing.com%') t, MGMT_METRICS M, MGMT_METRICS_RAW R
WHERE
T.TARGET_TYPE = M.TARGET_TYPE and
T.TARGET_GUID = R.TARGET_GUID and -- RAW DATA
R.METRIC_GUID = M.METRIC_GUID -- RAW DATA
group by T.TARGET_NAME,M.METRIC_NAME


It gives multiple rows of duplicate rows.
May anyone help me ?
Re: How to avoid duplicate rows from the result of a query ? [message #343135 is a reply to message #343132] Tue, 26 August 2008 07:11 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Very difficult to make a guess without the test case.Difficult to assume what the straucture is.



-Check join conditions throughly..
T.TARGET_GUID = R.TARGET_GUID and -- RAW DATA
R.METRIC_GUID = M.METRIC_GUID -- RAW DATA

- check using DISTINCT


Regards,
Oli


Re: How to avoid duplicate rows from the result of a query ? [message #343145 is a reply to message #343132] Tue, 26 August 2008 07:49 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
SELECT T.TARGET_NAME,M.METRIC_NAME
FROM <...>
group by T.TARGET_NAME,M.METRIC_NAME

> It gives multiple rows of duplicate rows.

Define "duplicate rows". As you aggregate by the displayed columns, you will not get the same (TARGET_NAME, METRIC_NAME) values twice.

> May anyone help me ?

Help with what? Shall we deduce current table structures, sample data, the required output and why this query does not fulfil it (as you did not post this information)?
Re: How to avoid duplicate rows from the result of a query ? [message #343146 is a reply to message #343132] Tue, 26 August 2008 08:02 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
hi,

as olivia suggest, is there any problem using distinct.

yours
dr.s.raghunathan
Re: How to avoid duplicate rows from the result of a query ? [message #343159 is a reply to message #343146] Tue, 26 August 2008 08:48 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
Simply throwing a distinct into a broken query does not fix the query it just hides the fact that it is broken. He needs to analyze his joins or post sample and structure data so he could be helped in that analysis. Distinct is a band-aid and over use can come back to bite you (or the person who comes after you) later.
Re: How to avoid duplicate rows from the result of a query ? [message #343168 is a reply to message #343159] Tue, 26 August 2008 09:12 Go to previous message
Olivia
Messages: 519
Registered: June 2008
Senior Member
I concur.
Need to have a look at the joins.Without knowing structure data its really difficult.Does the columns using in where clause contains null values?

Please provide some test case,It will be easier to find the problem.


Regards,
Oli
Previous Topic: multiple grouping required vertically from single table
Next Topic: Different between TRUNC AND ROUND FUNCTION?
Goto Forum:
  


Current Time: Fri Dec 09 17:29:30 CST 2016

Total time taken to generate the page: 0.21308 seconds