Step Bi Step

Last week, I have faced an interview and I was asked to write a SQL query to display an ordered column in an unsorted table and the next column should be displayed the ranking number. See below:

Unsorted table:                                                                  Sorted table:

Looks like pretty simple no? but in an interview with 3 Interviewers little  bit difficult! I did answer in a different way but there are pretty straight forward methods available in the SQL Server. We will see all the methods here:

Step 1: Create a table

CREATE TABLE [dbo].[Table1](
[Column1] [varchar](10) NOT NULL
)

Step 2: Insert the values

INSERT INTO [dbo].[Table1]([Column1]) VALUES ('C')
INSERT INTO [dbo].[Table1]([Column1]) VALUES ('D')
INSERT INTO [dbo].[Table1]([Column1]) VALUES ('B')
INSERT INTO [dbo].[Table1]([Column1]) VALUES ('E')
INSERT INTO [dbo].[Table1]([Column1]) VALUES ('A')
INSERT INTO [dbo].[Table1]([Column1]) VALUES ('F')

Method 1: Getting the ASCII value of the character and then display the number. Eg: ASCII of ‘A’ is 65, so the value is 65-64=1.

SELECT Column1,
 ASCII(Column1)-64 AS Column2
FROM Table1
ORDER BY Column1

Method 2: Using the ROW_NUMBER ( ) function and order by the column. You can check the function here.

SELECT Column1,
 ROW_NUMBER()OVER (ORDER BY Column1) AS Column2
FROM Table1

Method 3: Using the RANK ( ) function and order by the column. You can check the function here.

SELECT Column1,
 RANK() OVER (ORDER BY Column1) AS Column2
FROM Table1

Output:

That’s all! These are pretty easy methods, if we know about these then we can answer quickly.

Thanks. R./