Order against two columns at the same time (intersecting)

Question

I have a table with the fields CommonName and FirstName. Only either field has data, never both. Is there a way to order rows in an intersecting manner on SQL Server?

Example:

CommonName FirstName
Bern
           Wade
Ashley
Boris
           Ayana

I want records ordered like this:

CommonName FirstName
Ashley
           Ayana
Bern
Boris
           Wade

Is this possible, and if so, how?

score 2 · Answer 1 · answered May 25 '10 at 17:45

2

Use a CASE statement to select the value for that row and ORDER BY that.

answered May 25 '10 at 17:45

Hank Gay

70,339
36
160
222

score 2 · Answer 2 · answered May 25 '10 at 18:02

2

ORDER BY
  CASE
    WHEN CommonName is null
    THEN FirstName
    ELSE CommonName
  END

answered May 25 '10 at 18:02

Amy B

108,202
21
135
185

score 1 · Answer 3 · answered May 25 '10 at 17:47

1

ORDER BY CommonName + FirstName, with appropriate ISNULL(<column>, '') if they are nullable.

answered May 25 '10 at 17:47

AakashM

62,551
17
151
186

score 1 · Answer 4 · answered May 25 '10 at 19:27

1

order by coalesce(CommonName, FirstName)

answered May 25 '10 at 19:27

msi77

1,602
1
11
10

Order against two columns at the same time (intersecting)

4 Answers4

Linked