3

Suppose I have a column x with values A and B. For example:

Row X
1   A
2   A
3   A
4   B
5   B
6   B

Now, I want to sort them into this fashion:

Row X
1   A
2   B
3   A
4   B
5   A
6   B

Of course, I can create a new Column Y. Then assign A rows to odd number (1, 3, 5, ...), and B rows to even number (2, 4, 6, ...), and then sort with the Y column. But is there an easier way to achieve this?

It will be best if the answer can work with MS SQL.

1
  • 1
    Does your table have a key? It would be useful to know. Your column X contains duplicates and there appears to be nothing else to define the ordering of the table with. – nvogel Mar 5 '15 at 21:17
9

Try this:

CREATE TABLE SortTest (val char(1))

INSERT INTO SortTest VALUES 
('A'),
('A'),
('A'),
('B'),
('B'),
('B')

SELECT val
FROM SortTest
ORDER BY row_number() OVER (PARTITION BY val ORDER BY val), val
3
  • Doesn't work on MySQL 5.7 – Nikita240 Jun 6 '19 at 7:52
  • 1
    It's specifically for SQL Server. If you look at the tags under the original question most of the time they have what DB system it's for since they are all so different. – Kenneth Fisher Jun 7 '19 at 14:37
  • Can confirm that it works in Postgres as well – Jan Sommer Jan 30 at 17:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.