[Solved] Compare values in SQL using CASE, if match return and exit from CASE statement [closed]

by


I will use postgres for this example, I use row_number() and cte, if you have mySql will need a workaorund

You need a table priority

CREATE TABLE priority
    ("PhoneType" int, "Priority" int)
;

INSERT INTO priority
    ("PhoneType", "Priority")
VALUES
    (7, 1),        (6, 2),
    (1, 3),        (2, 4),
    (3, 5),        (4, 6),
    (5, 7),        (8, 8),
    (9, 9)    ;

Then put a rownumber to each phonetype acording to priority

WITH cte as (
    SELECT 
          p.*,
          pr."Priority",
          row_number() over (partition by "PersonID" ORDER BY "Priority") as rn
    FROM person p
    JOIN priority pr
      ON p."PhoneType" = pr."PhoneType"
    ORDER BY pr."Priority"
)
SELECT 
    c."PersonID",
    c."PhoneType", 
    c."PhoneNumber",
    CASE rn 
       WHEN 1 THEN 1
       ELSE NULL
    END as rn
FROM cte c

SqlFiddle Demo

OUTPUT

| PersonID | PhoneType | PhoneNumber |     rn |
|----------|-----------|-------------|--------|
|        1 |         7 |  7735487695 |      1 |
|        1 |         1 |  7731234569 | (null) |
|        1 |         5 |  7731547895 | (null) |

NOTE: I also change type 6 => 5 in your sample to highlight even more how the priority is working

After your edit SQL Server verion without table Fiddle

With Priority as (
    SELECT 7 as PhoneType,  1 as Priority UNION ALL
    SELECT 6 as PhoneType,  2 as Priority UNION ALL
    SELECT 1 as PhoneType,  3 as Priority UNION ALL
    SELECT 2 as PhoneType,  4 as Priority UNION ALL
    SELECT 3 as PhoneType,  5 as Priority UNION ALL
    SELECT 4 as PhoneType,  6 as Priority UNION ALL
    SELECT 5 as PhoneType,  7 as Priority UNION ALL
    SELECT 8 as PhoneType,  8 as Priority UNION ALL
    SELECT 9 as PhoneType,  9 as Priority 
), 
cte as (
    SELECT 
          p.*,
          pr.Priority,
          row_number() over (partition by PersonID ORDER BY Priority) as rn
    FROM Person p
    JOIN Priority pr
      ON p.PhoneType = pr.PhoneType    
)
SELECT 
    c.PersonID,
    c.PhoneType, 
    c.PhoneNumber
FROM cte c
WHERE rn = 1

OUTPUT

| PersonID | PhoneType | PhoneNumber |
|----------|-----------|-------------|
|        1 |         7 |  7735821547 |
|        2 |         1 |  7731547841 |

2

solved Compare values in SQL using CASE, if match return and exit from CASE statement [closed]