Hello Newsgroup readers,

Most of the time I had seen people looking for some solution to remove
duplicate rows where table is not having any key ( by defination it is
not a table).

Let's say I am having a table EMP (with two columns for illustration)
EMP
*****************
Name  City  ......
Jatinder Pune  ..... etc.
Jatinder Pune
Jatinder Pune
Jatinder Pune
Jatinder Pune
Jatinder Pune

To remove these duplicate rows what we normally do is use group by. but
there is another trick we may use (UNION)

Select * into tmpTable from
(
Select * from EMP
UNION
Select * from EMP
) XY
--- Note that no GROUP BY is required.

With Warm Regards
Jatinder Singh
http://jatindersingh.blogspot.com
http://sqloracle.tripod.com

Hi Jatinder,
    From an academic perspective, its an interesting solution indeed though
its a pretty.. hmmm.. new...  radical approach...
other equally interesting approaches (without group by) maybe ...  

Select * into tmpTable from
(
Select * from EMP
UNION
Select * from EMP where 1 = 0
) XY


Select distinct * into tmpTable from EMP

I'm kind of partial to this method...

USE Northwind
GO

INSERT INTO Employees ( FirstName, LastName ) VALUES ( 'Andrew', ' Fuller' )
INSERT INTO Employees ( FirstName, LastName ) VALUES ( 'Andrew', ' Fuller' )
INSERT INTO Employees ( FirstName, LastName ) VALUES ( 'Andrew', ' Fuller' )

SELECT DISTINCT
           FirstName
         , LastName
      FROM Employees
      WHERE 2 <> 3

Show quoteHide quote

Arnie Rowland wrote:
Show quoteHide quoteOkay, I'm going to sound stupid here, but what's the last line for? Is
that some sort of cool optimiser trick, or are you being
tongue-in-cheek?

Thanks all,

Yes.
Select distinct * .... is one method.
and frankly I didnot give it a thought.
Nice Approach!!!

What you did with UNION method by providing a WHERE 1=0 is also I had
not given a thought.


With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
http://sqloracle.tripod.com

    You could make this a bit more efficient by doing:

Select * into tmpTable from
(
Select * from EMP
UNION
Select * from EMP WHERE 1=0
) XY

.... but honestly, I'd just use DISTINCT instead.  It does the exact same
thing under the covers, and is a more maintainable solution.  How well do
you think some other developer will understand this code the first time they
see it?  With DISTINCT, you don't have that problem.

jsfromynr wrote:
Is that a trick when you want to write more code than needed?

Perhaps some time in the future you folks will look back on this
thread and appreciate the humor of it.For those that recognize this
as threatre of the absurd be gentle.Because you and almost everyone
else in the industry has bought a ticket to this play at some time or other.
www.rac4sql.net

I support Dataphor (www.alphora.com) as a RAD and a true Relational dbms.
I support MS Sql Server as a storage device for Dataphor.

Timeout - max pool size reached
CLR and Insert
Performance problem with SQL Server 2005
How to do a restore a .BAK database - SQL 2005
BCP error - "Unexpected EOF encountered" when using native format