Several methods of SQL filtering duplicate data

Often encountered in the development of data on the duplication of records, we have to find ways to filter a number of duplicate records, the following I summed up several methods, hope for everyone useful.

Method One,

Select Identity (int,1,1) as id,* into #temp from tabel
SELECT * from #temp where ID in (
Select Max (ID) from #emp where has count (*) >1 GROUP by col1,col2,col3 ...)

Instance

There's a table structure that's like this

ID bookname CBS ZZ
001 ASP Course Daquan Tsinghua University Press Tian Zhengping
002 Database tutorial System Tutorial Higher Education publishing house Zhou Oiying
003 ASP Daquan Tsinghua University Press Tian Zhengping

Now I want to do a query, to find out the fields as follows

BookName, CBS, ZZ

Filter records by BookName field, but to display multiple fields

The result of the query should be

BookName CBS ZZ
ASP Daquan Tsinghua University Press Tian Zhengping
Database system Tutorial Higher Education Press Zhou Oiying

But I do not know how to spell the SQL statement, please guide

Nononono (null,null): A method that solves a problem that is completely duplicated, and if there are several fields that do not repeat
but what do you want to show?
For example, if you want to display
          id      in the previous example             bookname                               cbs                              zz        new_id
(Duplicate records are new_id, but others do not repeat with other new_id Records)
and the ID displays the maximum value. What should be done.
General use

Select max (ID) ID, max (bookname)
Bookname,max (CBS) CBS, Max (ZZ) ZZ, new_id from table group by new_id

The field followed by the GROUP BY clause is the condition that you use to determine the repetition, such as only col1, so as long as the content of the Col1 field is the same as that of the record.

col1+ ', ' +col2+ ', ' ... col5 Union primary key

select * FROM table where col1+ ', ' +col2+ ', ' ... col5 in (
Select Max (col1+ ', ' +col2+ ', ' ... col5 ') from table
Where has count (*) >1
GROUP BY Col1,col2,col3,col4
)

/////////

select   t1.bookname,t1.zz,t1.cbs   from   table   T1
join   (   select   min (id)    as   id,bookname   from    Table
              group    by   bookname  )    as   T2
on   t1.bookname   =   t2.bookname   and   t1.id   =   t2.id