Posts Tagged ‘Interview Question’

SQL Server Code Name

SQL Server Code Name

Yesterday, I came across a very interesting question on one of the SQL Servers Online Forums.

Question – What is the code name of SQL Server 2005?

Well I knew that the Microsoft gives code names to it products when they are in developmental stages. But I never came across the code name for SQL Serves!

So I did some research on Net to find code name of all the SQL Server Release till date. They are listed below.

SQL Server Release Project Code Name
SQL Server 6.0 SQL95
SQL Server Enterprise Manager Starfighter
SQL Server 6.5 Hydra
SQL Server 7.0 Sphinx
SQL Server 7.0 Plato
SQL Server 2000 (32-bit) Shiloh
SQL Server 2000 (64-bit) Liberty
SQL Server Reporting Services Rosetta
SQL Server 2005 Yukon
SQL Server 2005 Mobile Edition Laguna
SQL Server 2008 Katmai / Akadia
SQL Report Designer 2.0 Blue
SQL Server 2008R2 Killimanjaro

 

Good Luck!

Advertisements

SQL Server – Find Nth Highest or Lowest Value in Table

Find Nth Highest or Lowest Value in Table

This one is a tricky question. We all assume that this is not a big deal, but when this question is asked in the interviews, we try all the permutations and combinations of SELECT statements, but are never close to the answer.

There are many solutions to this problem. I have listed down some of the simple methods to find out Nth highest and Nth Lowest values in the table.

Method 1

USE AdventureWorks;

— Finding unique third highest UnitPrice value from table

SELECT TOP 1 UnitPrice FROM

(SELECT DISTINCT TOP 3 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice DESC) AS A

ORDER BY UnitPrice;

— Finding unique second lowest UnitPrice value from table

SELECT TOP 1 UnitPrice FROM

(SELECT DISTINCT TOP 2 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice) AS A

ORDER BY UnitPrice DESC;

Method 2

— Finding unique third highest UnitPrice value from table

SELECT TOP 1 UnitPrice FROM Purchasing.PurchaseOrderDetail WHERE UnitPrice IN

(SELECT DISTINCT TOP 3 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice DESC)

ORDER BY UnitPrice;

— Finding unique second lowest UnitPrice value from table

SELECT TOP 1 UnitPrice FROM Purchasing.PurchaseOrderDetail WHERE UnitPrice IN

(SELECT DISTINCT TOP 2 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice)

ORDER BY UnitPrice DESC;

Method 3

— Finding unique third highest UnitPrice value from table

SELECT MIN(UnitPrice) FROM

(SELECT DISTINCT TOP 3 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice DESC) AS A;

— Finding unique second lowest UnitPrice value from table

SELECT MAX(UnitPrice) FROM

(SELECT DISTINCT TOP 2 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice) AS A;

Method 4

— Finding unique third highest UnitPrice value from table

SELECT MIN(UnitPrice) FROM Purchasing.PurchaseOrderDetail WHERE UnitPrice IN

(SELECT DISTINCT TOP 3 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice DESC);

— Finding unique second lowest UnitPrice value from table

SELECT MAX(UnitPrice) FROM Purchasing.PurchaseOrderDetail WHERE UnitPrice IN

(SELECT DISTINCT TOP 2 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice);

Method 5

— Finding unique third highest UnitPrice value from table

SELECT MAX(P1.UnitPrice) FROM Purchasing.PurchaseOrderDetail P1

WHERE 3 <= (SELECT COUNT(DISTINCT P2.UnitPrice)

FROM Purchasing.PurchaseOrderDetail P2 WHERE P1.UnitPrice <= P2.UnitPrice);

Method 6

— Finding unique third highest UnitPrice value from table

SELECT DISTINCT P1.UnitPrice FROM Purchasing.PurchaseOrderDetail P1

WHERE 3 = (SELECT COUNT(DISTINCT P2.UnitPrice)

FROM Purchasing.PurchaseOrderDetail P2 WHERE P1.UnitPrice<=P2.UnitPrice);

Hope you find this useful.

Good Luck!