SQL Tip #10 – Selecting Random Rows From a Table

Finding random records from a table might not be the important one. However, if you ever find yourself in a position to find random records from a table, then following could be a help:

Below sql queries will return random three(3) records from a table

Method 1: Using NEWID()
NEWID() will help us to generate random result set in a simple way. It creates a unique value of type uniqueidentifier. It will unique for each call. When it is a part of ORDER BY clause then it generates a unique value in memory for each row and then sorted in ascending order.

SELECT TOP 3 * FROM Customer ORDER BY NEWID()

 

Method 2: Using Tablesample()
TABLESAMPLE can quickly return a sample from a large table. TABLESAMPLE clause can be added to any table in the FROM clause. Percentage of rows or number of rows need to be specified as a part of this clause. However, this sampling method will return an approximate number of rows back. If you specify a small number, you might not be able to see any results. Rows returned by this clause are not in order and most likely to be random.

SELECT TOP 3 * FROM Customer Tablesample(1 percent)

If you want to get trully random records then user NEWID().

Advertisements

SQL Tip #9 – All I know about SQL NULL

NULL indicates missing/unknown/inapplicable/absent.

If the above statement makes you think that NULL is useless, then you are mistaken. This is one of the thing that no database developer can avoid it. So it very important to understand how NULL behaves differently in various situations.

Simply we can say, NULL is a placeholder for missing or unknown values at any given time.

At the time of the table creation if we know that values for certain columns won’t be known or will be missing during record insertion then we can make those columns to allow NULL. Later NULL could be replaced with the meaningful values.

Takeaway for NULL:

  • It is very important not to get confused NULL as an space or empty string.
  • NULL is not equal to NULL.
  • Any operation involving NULL will return NULL
  • Comparison operators (<, >, =, <>) cannot be used to find records with the NULL values. IS NULL or IS NOT NULL operators is the way to go.
  • NULL will be ignored for most of the aggregate functions. However, COUNT(*) will include NULL values.

 

Following examples below will return NULL:

SELECT 'Sumon' + NULL
SELECT 0 + NULL
SELECT 10 * NULL

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.

SQL Tip #7 – Formatting numbers with leading zeros (Left Padding)

In order to format a number with leading zeros, one of the following solutions could be used:

Solution 1:

DECLARE @number INT
DECLARE @length INT
SET @number = 123
SET @length = 10

SELECT REPLACE (STR( @number, @length ), ' ' , '0' ) 
       AS NewNumber

Solution 2:

DECLARE @number INT
DECLARE @length INT
SET @number = 123
SET @length = 10

SELECT RIGHT('0000000000'+ 
       CONVERT(VARCHAR,@number),@length) 
       AS NewNumber 

Solution 3:

DECLARE @number INT
DECLARE @length INT
SET @number = 123
SET @length = 10

SELECT REPLICATE('0',@length - LEN(@number)) + 
       CONVERT(VARCHAR,@number) 
       AS NewNumber