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.
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.......:-)
Answer:-
We have got three method to solve this problem.Thanks to Aviral Lamba and Sean Senneka for giving the correct answer.
Answer given by Aviral lamba
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
Answer Given by Sean Senneka
Answer 1 :-
SELECT charactername
, ROW_NUMBER() OVER (PARTITION BY charactername ORDER BY charactername) ordby
FROM tbl_charactername
ORDER BY 2,1
Answer 2:-
SELECT charactername
FROM tbl_charactername
ORDER BY ROW_NUMBER() OVER (PARTITION BY charactername ORDER BY charactername) ASC,charactername DESC
For me the second answer given by Sean is the best as it neither use any hardcore value and also gives the desired column.
