Tags: "sql", access_time 1-min read

Edit this post on Github

Top Useful Sql Templates

In this post, we introduce the most useful SQL template statements.

Find all duplicates based on a field

SELECT [EmailAddress], [CustomerName] FROM [Customers] WHERE [EmailAddress] IN
  (SELECT [EmailAddress] FROM [Customers] GROUP BY [EmailAddress] HAVING COUNT(*) > 1)

This is significantly more efficient than using EXISTS

Select DISTINCT on multiple columns

We can select distinct on multiple columns(distinct combinations of column values.), for example, gender(male) AND age(>18).

Select duplicate rows based on column

SELECT TOP 1 C1.ID, C1.Code, C1.OwnerName, C2.NumberDuplicates
Customers C1
  SELECT Code, COUNT(*) as NumberDuplicates  FROM Customers GROUP BY code HAVING COUNT(*) > 1
  ) C2
ON C1.Code = C2.Code

Override column values conditionally

SELECT id, name, CASE WHEN hide = 0 THEN 'false' ELSE 'true' END AS hide