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

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…….:-)

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in Interview Questions and tagged . Bookmark the permalink.

32 Responses to Question of the Week – 28/12/2013 – Question 2

  1. Aviral Lamba says:

    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

  2. admin says:

    Thanks Aviral

  3. Sean Senneka says:

    SELECT charactername
    , ROW_NUMBER() OVER (PARTITION BY charactername ORDER BY charactername) ordby
    FROM tbl_charactername
    ORDER BY 2,1

    • Sean Senneka says:

      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

    • admin says:

      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.

  4. admin says:

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

  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

  6. Pingback: How to watch Rugby World Cup 2019 live stream online instantly

  7. Pingback: คาสิโนออนไลน์

  8. Pingback: เว็บคาสิโน

  9. Pingback: 카지노사이트

  10. Pingback: xem bongdatructiep

  11. Pingback: Creative Agency in Orlando

  12. Pingback: click

  13. Pingback: 바카라

  14. Pingback: arboldelsilencio.com

  15. Pingback: Empire Market

  16. Pingback: https://internetnews.net.pl/echo-serca-plodu-zalecane-przeprowadzenie-badania-czym-polega/

  17. Pingback: link bong88

  18. Pingback: social impact marketing

  19. Pingback: обезкосмяване на лице с епилатор

  20. Pingback: cute dancing

  21. Pingback: Dream Market

  22. Pingback: Allen Key Market Segmented by Product, Top Manufacturers, Geography Trends & Forecasts to 2025

  23. Pingback: market research report companies

  24. Pingback: coverage

  25. Pingback: www.indoqqpoker.site

  26. Pingback: 2020 Cover Economist

  27. Pingback: pokermas

  28. Pingback: www.1qiuqiu99.club

  29. Pingback: paris qq

Leave a Reply