Sorting a column alphabetically and display the ordered number in the next column – SQL Server

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./

Advertisements

, , , , ,

  1. #1 by tero powerleveling on July 3, 2012 - 8:04 pm

    I admired your insightful post. good work. I hope you produce many. I will continue subscribing

  2. #2 by Robin on July 15, 2014 - 1:07 pm

    Hi there! Would you mind if I share your blog with my zynga group?
    There’s a lot of folks that I think would
    really appreciate your content. Please let me know. Thank you

  3. #3 by Rajanihanth on July 15, 2014 - 1:26 pm

    Sure..! 🙂

Please 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

%d bloggers like this: