CODEWITHSUNDEEP
sql-server
jackstraw22
jackstraw22

Reputation: 641

SQL window function ordering issue

I have a dataset that can have a location with multiple specialties. I'm trying to just choose one specialty based on the following criteria:

  1. If Primary = 'Y' then choose Primary over Boardcertified
  2. If Primary = 'N' and Boardcertified = 'Y', then choose Boardcertified

  3. If both = 'N' then chose either record

    declare @t table(id int, location varchar(250), specialty varchar(250), boardcertified char, primaryspeciality char)

insert into @t values(1, 'Dallas', 'Maternal', 'Y', 'N'),
                     (1, 'Dallas', 'Obstetrics', 'Y', 'Y'),
                     (2, 'Plano', 'Maternal', 'Y', 'N'),
                     (2, 'Plano', 'Peds', 'N', 'N'),
                     (3, 'Arlington', 'Peds', 'N', 'N'),
                     (3, 'Arlington', 'Maternal', 'Y', 'Y')

I'm been trying to solve this with a window function, like this:

    select * from
    (
    select *, row_number()over(partition by id, location
                               order by case when primaryspeciality = 'Y' then 1
                                         when boardcertified = 'Y' then 2
                                         end desc) as rn
from @t)a
    where rn = 1

This gives me the following result:

    id    location   specialty    boardcertified   primaryspecialty 
    1       Dallas     Maternal     Y                 N

What I actually want is:

    id     location   specialty    boardcertified   primaryspecialty
    1       Dallas    Obstetrics      Y               Y

Since primaryspecialty = 'Y' for Dallas and Obstretics I would want that record. I'm not sure what I'm doing wrong here.

Upvotes: 0

Views: 59

Answers (1)

Thom A
Thom A

Reputation: 95561

I think this is what you're after:

WITH CTE AS(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY id, [location]
                              ORDER BY CASE primaryspeciality WHEN 'Y' THEN 0 ELSE 1 END,
                                       CASE boardcertified WHEN 'Y' THEN 0 ELSE 1 END,
                                       id) AS RN
    --If you want the id to be random each time when they are both 'Y' (as you said it could be any), replace id with NEWID()
    FROM @t)
SELECT *
FROM CTE
WHERE RN = 1;

Note the uses of multiple CASE expressions.

Upvotes: 1

Related Questions