SQL Sorting Data into a Pattern

Not sure if this is possible, but there might be a creative approach...

Given this data in SQL Server 2005:

AAA

AAA

BBB

BBB

CCC

CCC

DDD

DDD

How could 开发者_StackOverflowI return a result set sorted in a pattern like this:

AAA

BBB

CCC

DDD

AAA

BBB

CCC

DDD

---

If your column were called "col", and your table were named "table", I would try something like this:

WITH Indexes AS (
    SELECT 
    ROW_NUMBER() OVER (PARTITION BY col ORDER BY col) as __row,
    col
    FROM table)
SELECT col
FROM Indexes
ORDER BY __row, col;

---

Don't know if it works, but in Oracle I would try to create a view in which you use ROWNUM in the query of your view.

Then query the view and sort on:

• rownum modulo 2
• the string

I don't have my database at hand here to test this, but this tip might give you some ideas.

---

Mysql, assuming you have table T(a varchar); with the data you provided:

select @t:=a from T order by @t <> a;

it works :-)