Home » SQL & PL/SQL » SQL & PL/SQL » without distinct (oracle 10g)
without distinct [message #425955] Tue, 13 October 2009 06:57 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
rajasekhar857 wrote on Tue, 13 October 2009 13:57
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.

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 Go to previous messageGo to next message
_jum
Messages: 509
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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
_jum wrote on Tue, 13 October 2009 14:31
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


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

Re: without distinct [message #425984 is a reply to message #425983] Tue, 13 October 2009 09:18 Go to previous message
_jum
Messages: 509
Registered: February 2008
Senior Member
I totally agree with You, this is "abuse" of DISTINCT and only an answer to a kind of stupid interview question.
Previous Topic: Issue with multiple column Sub-Query
Next Topic: What do they mean by 8 integrity constraints?
Goto Forum:
  


Current Time: Sat Dec 10 09:25:53 CST 2016

Total time taken to generate the page: 0.08895 seconds