SQL Tip #8 – Only Unique Records

Once I had the requirements to pick a customer per city. In order to solve this problem I came up with the following solutions.

Sample table 1:

Output 1:

 

Solution 1:

SELECT Main.* FROM Customer Main
INNER JOIN
	(
		SELECT MIN(CustomerID) AS CustomerId
		FROM Customer GROUP BY City
	) Sub
ON Main.CustomerId = Sub.CustomerId
ORDER BY Main.CustomerId

 

Solution 2:

SELECT Main.* FROM Customer Main
CROSS APPLY
	(
		SELECT MIN(CustomerID) AS CustomerId
		FROM Customer
		WHERE City = Main.City
		GROUP BY City
	) Sub
WHERE Main.CustomerId = Sub.CustomerId
ORDER BY Main.CustomerId

 

Solution 3:

SELECT Main.* FROM Customer Main
CROSS APPLY
	(
		SELECT ROW_NUMBER()
		OVER(PARTITION BY City
		     ORDER BY CustomerId ASC) AS Rnk,
		CustomerId
		FROM Customer
	) Sub
WHERE Main.CustomerId = Sub.CustomerId
      AND Sub.Rnk = 1
ORDER BY Main.CustomerId

 

Solution 4:

SELECT CustomerId,CustomerName,City FROM
(
		SELECT ROW_NUMBER()
		OVER(PARTITION BY City
		     ORDER BY CustomerId ASC) AS Rnk,
		*
		FROM Customer
) Main
WHERE Main.Rnk = 1
ORDER BY Main.CustomerId

 

Performance of these query will vary based on the database size, indexing etc. You can pick the query that suits you most.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s