:::: MENU ::::

Posts Categorized / T-SQL Tips and Tricks

  • Jan 07 / 2010
  • 0
dbDigger, T-SQL Interview Questions, T-SQL Tips and Tricks

Finding Nth highest value in a column through T-SQL

Here is a situation, to find a specific highest value in a column. Most common example may be that of a salary column where one may need 7th highest salary.
To make short of this short problem i have selected pubs..Sales.qty column to work with.
Apply following query and you will get total 11 distinct values arranged

 
USE Pubs  
 GO  
 SELECT DISTINCT qty  
 FROM sales  
 ORDER by qty DESC  
 GO  

Now suppose we have to find 7th highest value in qty column. The simple query for this is

 SELECT min(qty)  
 FROM sales  
 WHERE qty IN  
 (SELECT distinct TOP 7 qty  
 FROM sales  
 ORDER BY qty DESC)  
 GO  

So its done.
It is appropriate to mention here another query that i found on a blog.

 
SELECT TOP 1 qty  
 FROM ( SELECT DISTINCT TOP 7 qty  
 FROM sales  
 ORDER BY qty DESC) ABC  
 ORDER BY qty ASC  
 GO  

So in above queries you may find nth highest value where n is always greater than 1.
That is all for finding nth highest value in a column.

  • Jan 07 / 2010
  • 1
dbDigger, T-SQL Interview Questions, T-SQL Tips and Tricks

Call it ad-hoc or out of box thinking

Although is does not always work but, some times ad-hoc thinking makes the solution very simple. Consider the following scenario for which a very simplified solution is provided for. We may call it out of box thinking as well.

Question:
Is there a condition for the WHERE clause to display the details about the students born in the month numbers of 1[Jan], 3,4,5,7,8? The condition should not use both IN and OR operators.

Answer: Yes

Explanation: We can give the condition as follows:
where datename(m,dob) not like ‘%e%’
because these month(s) specified do not have the letter ‘e’, but all the other months have at least one ‘e’ in month name.

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

  • Jan 07 / 2010
  • 0
dbDigger, T-SQL Interview Questions, T-SQL Tips and Tricks

Do not run until answer

Question:
What do you expect the result of the following query to be? No cheating, don’t run until you’ve answered!

 WITH DATA (Numbers) AS  
 (SELECT NULL UNION ALL  
 SELECT NULL UNION ALL  
 SELECT NULL UNION ALL  
 SELECT 1 UNION ALL  
 SELECT 2 UNION  
 SELECT 3)  
 SELECT COUNT(ALL Numbers) AS NULLNumberCount FROM DATA  
 WHERE Numbers IS NULL  

The final UNION negates the duplicates selected with the previous UNION ALL statements. The COUNT(ALL expression) evaluates the expression for each row in a group and returns the number of nonnull values. ALL Applies the aggregate function to all values. ALL is the default.For investigation, change the final UNION to UNION ALL, and the SELECT COUNT (ALL Numbers) to COUNT (*)


Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

  • Jan 07 / 2010
  • 1
dbDigger, T-SQL Interview Questions, T-SQL Tips and Tricks

Interupting WAIT FOR in T-SQL

Question:

Which of the following items can be used to end a WAITFOR statement in SQL Server 2005?

Answer:

  • specific time
  • An elapsed period of time
  • A message in a Service Broker queue
  • A timeout

Explanation: All of these can actually end a WAITFOR statement. You can pick a particular time or an amount of time that elapses in the statement. You can also specify that the WAITFOR ends when a message is received in a Service Broker queue, or a timeout passes in waiting for that message.

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.

  • Jan 07 / 2010
  • 1
dbDigger, T-SQL Interview Questions, T-SQL Scripts, T-SQL Tips and Tricks

How to delete duplicate rows from a table

A DBA may be often required to delete duplicate rows from a SQL Server database table. Several methods can be used keeping in view the resources and exact situation. Here I will create a specific scenario. After going through this scenario, you will be able to remove the duplicate rows in any given scenario on ground.
Run following script to generate duplicate data in a table named “dups” created in any database that you select. Here I will use “pubs”.

 
-- Create table in any database  
 create table Dups  
 (col1 varchar(50),  
 col2 varchar(50),  
 col3 varchar(50))  
 GO  
 -- Populate the table for duplicate rows  
 INSERT INTO dups  
 SELECT 'Val1','val2','Val3'  
 UNION ALL  
 SELECT 'Val1','Val2','Val13'  
 UNION ALL  
 SELECT 'Val1','Val2','Val3'  
 UNION ALL  
 SELECT 'Val1','Val2','Val13'  
 UNION ALL  
 SELECT 'Val11','Val12','Val3'  
 UNION ALL  
 SELECT 'Val16','Val18','Val20'  
 UNION ALL  
 SELECT 'Val11','Val12','Val3'  
 UNION ALL  
 SELECT 'Val16','Val18','Val20'  
 UNION ALL  
 SELECT 'Val6','Val7','Val6'  
 GO  

Table is created and populated with duplicate rows in it now.
Total rows are 9
Distinct rows are 5, which we have to preserve at the end.
Now we have to remove all duplicate rows, leaving behind just 5 distinct rows in three columns.

Execute the following script to complete the operation

 
-- Remove duplicate rows  
 ALTER TABLE dups  
 ADD ser SMALLINT PRIMARY KEY identity(1,1)  
 GO  
 DELETE FROM dups  
 WHERE ser not in (SELECT max(ser) FROM dups  
 GROUP BY col1,col2,col3)  
 GO  

At the end now we have all duplicate rows removed and our table is with an identity column “ser”. You may also drop this column by using

 -- Drop the temporary column created  
 ALTER TABLE dups  
 DROP COLUMN ser  
 GO  

but for that you have to first drop the constraint attached with it. As in my case it is as follows

 -- Drop PK constraint  
 ALTER TABLE dups  
 DROP PK__dups__286302EC  
 GO  

So it is over. You may get include any number of columns in your distinct rows wish list. You may modify the ‘group by’ parameters to meet any number of columns to consider in duplicate row . Query uses the magic of ‘group by.’ Here it is appropriate to mention that “distinct” and “group by” both produce same result and almost same resource utilization. But one of important difference between both is that group by makes you able to apply an aggregate function on each group of data separately. That fact has used here with “max” in our duplicate removal query.

Click here to read an excellent article on this topic by Arshad Ali. It covers different ways to deal with duplicate rows.

Consult us to explore the Databases. Contact us