CODEWITHSUNDEEP
sqlsql-server-2008selectsql-order-by
Jonnny
Jonnny

Reputation: 5039

Unsure how to order this in SQL Server

I'm trying to order a result set, it appears really simple, but I'm having a little trouble.

A person can have multiple address types, NULL, Civic or Mailing. I want anyone who has a NULL address to listed at the top, but then their civic and mailing address to follow.

If you don't have a null address, then their civic and/or mailing address comes after anyone who has a null.

I've tried ordering by address will just lists all nulls first, ordering by name, address, will work in part, but won't guarantee that the people will nulls will be first.

Take this set for example

ID  Person      Address
-------------------------
1   Person A    Civic
1   Person A    Mailing
2   Person B    NULL
2   Person B    Civic
2   Person B    Mailing
3   Person C    NULL
3   Person C    Civic
3   Person C    Mailing

Should be:

ID   Person     Address
-------------------------
2   Person B    NULL
2   Person B    Civic
2   Person B    Mailing
3   Person C    NULL
3   Person C    Civic
3   Person C    Mailing
1   Person A    Civic
1   Person A    Mailing

Upvotes: 2

Views: 61

Answers (5)

Aman Verma
Aman Verma

Reputation: 51

You can try this one too

SELECT *
FROM @tab
ORDER BY Person,
         (CASE
              WHEN Address IS NULL
              THEN 'a'
              ELSE Address
          END);

Upvotes: 0

Serg
Serg

Reputation: 22811

Simply

select *
from mytable
order by min(case when Address is null then 0 else 1 end) over (partition by ID), id, Address;

Although Sql Server doesn't accept a window function in WHERE it's OK in ORDER BY. So you needn't any subquery here.

Upvotes: 0

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

Try with this CTE version.

;WITH CTE_1
  AS (SELECT Id, Person, Address, 
               SUM(CASE WHEN PERSON IS NULL THEN 1 ELSE 0 END) OVER 
              (PARTITION BY person) AS RNO
         FROM   addresses) 
  SELECT Id, Person, Address
  FROM CTE_1
  ORDER BY RNO DESC, person ASC, address ASC

Upvotes: 1

Mureinik
Mureinik

Reputation: 312257

Tim Biegeleisen has the right idea with regard to count the number of records where address IS NULL. I think the query could be made a tad more elegant (and probably perform a bit better) by using sum with a windows clause instead of joining on it with a subquery:

SELECT id, person, address
FROM   (SELECT id, 
               person, 
               address, 
               SUM(CASE WHEN PERSON IS NULL THEN 1 ELSE 0 END) OVER 
                  (PARTITION BY person) AS s
        FROM   addresses) t
ORDER BY s DESC, person ASC, address ASC

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

This solution uses a subquery which determines whether or not each ID (person) has a NULL value one his addresses. If a person does have a NULL value, then a tally kept will have a sum greater than zero. Those persons with at least one NULL address are then ordered first before those persons without at least one NULL. After this, the result set is ordered by ID followed by Address, as you might already have been doing.

SELECT t1.*
FROM yourtable
INNER JOIN
(
    SELECT ID,
           SUM(CASE WHEN Address IS NULL THEN 1 END) AS hasNull
    FROM yourTable
    GROUP BY ID
) t2
    ON t1.ID = t2.ID
ORDER BY CASE WHEN t2.hasNull > 0 THEN 0 ELSE 1 END,
         t1.ID,
         t1.Address

Upvotes: 2

Related Questions