Web Analytics Made Easy - Statcounter

Technology with Vivek Johari

Interview Questions

Question of the Week – 28/12/2013 – Question 2

Byvivekjohari

Dec 28, 2013 #Question of the Week
Question:- Suppose we have a table named tbl_charactername have only one column say “charactername”.
It contains 10 rows. First 5 rows contains “Vivek Johari” and the next 5 rows contains “Abhinav Golwalkar”
tbl_charactername
———————
Vivek Johari
Vivek Johari
Vivek Johari
Vivek Johari
Vivek Johari
Abhinav Golwalkar
Abhinav Golwalkar
Abhinav Golwalkar
Abhinav Golwalkar
Abhinav Golwalkar
Now we need a SQL Query which can return the result in the form
Desired Result:-
Vivek Johari
Abhinav Golwalkar
Vivek Johari
Abhinav Golwalkar
Vivek Johari
Abhinav Golwalkar
Vivek Johari
Abhinav Golwalkar
Vivek Johari
Abhinav Golwalkar

Query should work even if the table has 30 rows with first 15 contains “vivek johari” and next 15 contains “Abhinav Golwalkar” or 40 rows with first 20 contains “vivek johari” and next 20 contains “Abhinav Golwalkar” etc. But output should contains “Vivek Johari” and “Abhinav Golwalkar” in alternate rows.

Keep Answering…….:-)

Discover more from Technology with Vivek Johari

Subscribe to get the latest posts sent to your email.

By vivekjohari

I am currently working as a Senior Database Professional and have around 18 years of experience in database. Degree:- Master Degree in Computer(MCA) Certification course in Data Science & Machine Learning from Indian Institute of Technology (IIT), Delhi Work experience:- Designing of the database. Database Optimization. Writing Complex Stored Procedures,Functions,Triggers etc. Designing and developing SSIS & DTS packages. Designing SQL Reports using SSRS. Database Server Maintenance. Certification:- MCTS: DA-100: Analysing Data with Microsoft Power BI MCTS: DP-300: Administering Relational Databases on Microsoft Azure Microsoft certified Sql DBA in Sql server 2008 (MCTS). Microsoft certified BI professional in Sql server 2008 (MCTS). Oracle certified profession DBA in ORACLE 10g (OCP) certified profession DBA in ORACLE 9i (OCP) My other publication Technical Blog:- Technologies with Vivek Johari Guest Author and Blogger at sqlservercentral.com

Related Post

Interview Questions SQL Advanced SQL Indexes

Interview question – Is Clustered index on column with duplicate values possible?

Aug 13, 2015 vivekjohari
Interview Questions

Question of the Month (July)

Aug 3, 2015 vivekjohari
Interview Questions SQL Basic SQL Server

Script to find complete months between two given dates

Jul 10, 2015 vivekjohari
32 thought on “Question of the Week – 28/12/2013 – Question 2”

  1. SELECT CASE WHEN (A.ROW1 % 2)=0 THEN 'Vivek Johari' WHEN (A.ROW1 % 2)=1 THEN 'Abhinav Golwalkar' end PERSONNAME FROM(
    SELECT charactername,ROW_NUMBER()OVER (ORDER BY charactername)AS ROW1 FROM tbl_charactername )A

    Reply

    1. Just noticed your desired results are descending, and I can just put the function in the order by to create the shortest method:
      SELECT charactername
      FROM tbl_charactername
      ORDER BY ROW_NUMBER() OVER (PARTITION BY charactername) ASC,charactername DESC

      Reply

    2. Hi Sean,
      Thanks for giving two method for solving this problem. In the first answer we get two columns instead of one but it is correct answer as it gives the desired solution.

      Reply

  4. Hi Sean,
    Your Second answer is also correct and returns only one column but when we execute your query, it gives error since you do not include order by clause in the Row_Over() function. The Correct Query will be
    SELECT charactername
    FROM tbl_charactername
    ORDER BY ROW_NUMBER() OVER (PARTITION BY charactername ORDER BY charactername) ASC,charactername DESC

    Thanks for giving correct answer.. 🙂

    Reply

  5. I think this will work for all names instead of vivek and Abhinav….

    BEGIN TRAN
    DECLARE @MIN INT=1 ,@MAX INT= (SELECT COUNT(*) FROM TBL_CHARACTERNAME)
    CREATE TABLE #T1 (NAME VARCHAR(100) )
    WHILE (@MAX>@MIN) BEGIN
    INSERT INTO #T1 SELECT CHARACTERNAME FROM
    (SELECT CHARACTERNAME, ROW_NUMBER() OVER (ORDER BY CHARACTERNAME) E FROM TBL_CHARACTERNAME ) T WHERE E=@MAX
    INSERT INTO #T1 SELECT CHARACTERNAME FROM
    (SELECT CHARACTERNAME, ROW_NUMBER() OVER (ORDER BY CHARACTERNAME) E FROM TBL_CHARACTERNAME ) T WHERE E=@MIN
    SET @MIN=@MIN+1
    SET @MAX=@MAX-1
    END
    SELECT * FROM #T1
    DROP TABLE #T1
    COMMIT TRAN

    Reply

Leave a Reply

You missed

Miscellaneous SQL Server

🎉✨ Happy New Year 2025! ✨🎉

January 1, 2025 vivekjohari No Comments
SQL Server

Database Partitioning in SQL Server

December 31, 2024 vivekjohari No Comments
SQL Server

What are the important python libraries?

December 31, 2024 vivekjohari No Comments
SQL Server

Understanding Estimated and Actual Query Plans in SQL Server

December 31, 2024 vivekjohari No Comments

Discover more from Technology with Vivek Johari

Subscribe now to keep reading and get access to the full archive.

Continue reading