without distinct [message #425955] |
Tue, 13 October 2009 06:57  |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
hi my select query is giving some duplicate values as same row repeating twice.i know distinct clause will suppress duplicates.
but i dont want to use distinct in select query.any other alternative.
|
|
|
Re: without distinct [message #425957 is a reply to message #425955] |
Tue, 13 October 2009 07:00   |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Delete the duplicate rows?
If the duplicate rows aren't completely identical rewrite the where clause to eliminate one?
Otherwise I'd be going with distinct, and I'm really curious as to why you don't want to use it.
|
|
|
Re: without distinct [message #425959 is a reply to message #425955] |
Tue, 13 October 2009 07:10   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
rajasekhar857 wrote on Tue, 13 October 2009 13:57hi my select query is giving some duplicate values as same row repeating twice.i know distinct clause will suppress duplicates.
but i dont want to use distinct in select query.any other alternative.
Of course, it depends what is the reason of getting "duplicates" in the resultset.
If there are already "duplicate" rows in source data, there is no other way than using DISTINCT in the query without changing data (as cookiemonster suggested).
"Duplicates" may be also a result of lacking join condition(s) or joining with other tables which columns are not used in the SELECT list. In this case, adding missing join condition(s) or removing unused tables from the FROM clause is proper solution.
|
|
|
Re: without distinct [message #425975 is a reply to message #425955] |
Tue, 13 October 2009 08:31   |
_jum
Messages: 577 Registered: February 2008
|
Senior Member |
|
|
For casual purpose or interview You could use GROUP BY on all columns:
WITH data AS
(SELECT 'A' id, 1 nr FROM dual UNION ALL
SELECT 'A' , 1 FROM dual UNION ALL
SELECT 'B' , 1 FROM dual UNION ALL
SELECT 'B' , 1 FROM dual UNION ALL
SELECT 'B' , 2 FROM dual
)
SELECT id, nr FROM data
GROUP BY id, nr
ID NR
------------
A 1
B 1
B 2
[Updated on: Tue, 13 October 2009 08:32] Report message to a moderator
|
|
|
Re: without distinct [message #425983 is a reply to message #425975] |
Tue, 13 October 2009 09:12   |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
_jum wrote on Tue, 13 October 2009 14:31For casual purpose or interview You could use GROUP BY on all columns:
WITH data AS
(SELECT 'A' id, 1 nr FROM dual UNION ALL
SELECT 'A' , 1 FROM dual UNION ALL
SELECT 'B' , 1 FROM dual UNION ALL
SELECT 'B' , 1 FROM dual UNION ALL
SELECT 'B' , 2 FROM dual
)
SELECT id, nr FROM data
GROUP BY id, nr
ID NR
------------
A 1
B 1
B 2
You could but I'd personally say that that is just misleading - if you want distinct then you should use distinct, that's what it's there for. Group by exists for a different purpose.
EDIT: typo
[Updated on: Tue, 13 October 2009 09:13] Report message to a moderator
|
|
|
|