How to access the value of a function generated column in SQL

I have the following SQL

select count(*) col, column1, column2, column3 from TempTable group by column1, column2, column3 order by 1 desc

so the column generated by the count will return a numb开发者_Python百科er and there are 17 rows that do not have the number 1 (duplicate rows as columns 1, 2 and 3 are primary keys) and i want to delete any that have the count greater than 1?

You can use the having-clause:

select count(*) col, column1, column2, column3
from TempTable group by column1, column2, column3
having count(*) > 1
order by 1 desc

To delete:

delete tt
from TempTable tt
     inner join (select count(*) col, column1, column2, column3
                 from TempTable group by column1, column2, column3
                 having count(*) > 1) tmp
     on tmp.column1 = tt.column1
     and tmp.column2 = tt.column2
     and tmp.column3 = tt.column3

First you insert the data in temporary table:

select count(*) col, column1, column2, column3
into #temp
from TempTable group by column1, column2, column3 order by 1 desc

Then, you delete the data, and insert it from the #temp table:

delete from TempTable 
go

insert into TempTable select column1, column2, column3 from #temp
go