Skip to main content

Return to Answer

added 36 characters in body
Source Link
sticky bit
sticky bit
  • 37.4k
  • 12
  • 33
  • 46
SELECT x.id,
       x.name,
       x.location
       FROM (SELECT t.id,
                    t.name,
                    t.location,
                    row_number() OVER (PARTITION BY t.id
                                       ORDER BY CASE location
                                                  WHEN 'home' THEN
                                                    1
                                                  WHEN 'office' THEN
                                                    2
                                                  WHEN 'village' THEN
                                                    3
                                                  ELSE
                                                    4
                                                END)
                    FROM elbat) x
       WHERE x.rn = 1;

SELECT x.id,
       x.name,
       x.location
       FROM (SELECT t.id,
                    t.name,
                    t.location,
                    row_number() OVER (PARTITION BY t.id
                                       ORDER BY CASE location
                                                  WHEN 'home' THEN
                                                    1
                                                  WHEN 'office' THEN
                                                    2
                                                  WHEN 'village' THEN
                                                    3
                                                  ELSE
                                                    4
                                                END)) x
       WHERE x.rn = 1;

SELECT x.id,
       x.name,
       x.location
       FROM (SELECT t.id,
                    t.name,
                    t.location,
                    row_number() OVER (PARTITION BY t.id
                                       ORDER BY CASE location
                                                  WHEN 'home' THEN
                                                    1
                                                  WHEN 'office' THEN
                                                    2
                                                  WHEN 'village' THEN
                                                    3
                                                  ELSE
                                                    4
                                                END)
                    FROM elbat) x
       WHERE x.rn = 1;
Source Link
sticky bit
sticky bit
  • 37.4k
  • 12
  • 33
  • 46

You can use row_number() to assign each row a number in a given order per ID. Use a CASE to define the order by location.

SELECT x.id,
       x.name,
       x.location
       FROM (SELECT t.id,
                    t.name,
                    t.location,
                    row_number() OVER (PARTITION BY t.id
                                       ORDER BY CASE location
                                                  WHEN 'home' THEN
                                                    1
                                                  WHEN 'office' THEN
                                                    2
                                                  WHEN 'village' THEN
                                                    3
                                                  ELSE
                                                    4
                                                END)) x
       WHERE x.rn = 1;

Note: If the locations are only exactly the given three, than the CASE isn't needed, as they coincidentally already have the wanted order when sorted lexicographically. But I assume that was just an example and the coincidence might not be given in the real data.