SQL - ranking and assigning a type for a Id based on a preassigned rank for a column-CodePudding

I have a main car table. Each Id has multiple entries in the table, each with different car types and colors. Each car type has a rank, I have included that after the main table. My returned result, should have all the records from the main table along with a new column which has the highest ranked type for each Id.

Main Car Table:

Id	Type	Color
1	Sedan	Black
1	SUV	Blue
2	Hatchback	Red
2	Convertible	Blue
2	SUV	Orange
3	RUV	Yellow
3	MPV	Orange
3	Coupe	Brown

Car Type rank:

Type	Rank
Sedan	1
SUV	2
Convertible	3
Coupe	4
MPV	5
Hatchback	6

Required Result:

Id	Type	Color	ResultType
1	Sedan	Black	Sedan
1	SUV	Blue	Sedan
2	Hatchback	Red	SUV
2	Convertible	Blue	SUV
2	SUV	Orange	SUV
3	RUV	Yellow	Coupe
3	MPV	Orange	Coupe
3	Coupe	Brown	Coupe

Explanation: Since for Id 1, Sedan is the highest rank it has been added as the resultType for Id 1. For Id2 highest rank is SUV, for Id3 it is coupe.

CodePudding user response：

WITH main_car AS
  (
    SELECT 1 AS id, 'Sedan' AS type, 'Black' AS colour 
    UNION ALL
    SELECT 1 AS id, 'SUV' AS type, 'Blue' AS colour
    UNION ALL
    SELECT 2 AS id, 'Hatchback' AS type, 'Red' AS colour
    UNION ALL
    SELECT 2 AS id, 'Convertible' AS type, 'Blue' AS colour
    UNION ALL
    SELECT 2 AS id, 'SUV' AS type, 'Orange' AS colour
    UNION ALL
    SELECT 3 AS id, 'RUV' AS type, 'Yellow' AS colour
    UNION ALL
    SELECT 3 AS id, 'MPV' AS type, 'Orange' AS colour
    UNION ALL
    SELECT 3 AS id, 'Coupe' AS type, 'Brown' AS colour
  )
  ,car_type AS
  (
    SELECT 'Sedan' AS type, 1 AS rank 
    UNION ALL
    SELECT 'SUV' AS type,   2 AS rank 
    UNION ALL
    SELECT 'Convertible' AS type,   3 AS rank 
    UNION ALL
    SELECT 'Coupe' AS type, 4 AS rank 
    UNION ALL
    SELECT 'MPV' AS type,   5 AS rank 
    UNION ALL
    SELECT 'Hatchback' AS type, 6 AS rank
  )
 
SELECT 
  mc.*, 
  FIRST_VALUE(ct.type) OVER (PARTITION BY id ORDER BY rank) AS result_type
FROM 
  car_type ct
LEFT JOIN 
  main_car mc
  ON ct.type = mc.type

CodePudding user response：

One option could be:

joining the two tables to have one big table with car details
extracting the maximum value of the rank for each id partition
self joining to retrieve the name of the maximum-ranked car

Here's the query:

WITH cte AS (
    SELECT c.*,
           t.rank,
           MIN(t.rank) OVER(PARTITION BY Id) AS highest_rank
    FROM       Car c
    INNER JOIN CarType t
            ON c.Type = t.Type
)
SELECT t1.Id,
       t1.Type,
       t1.Color,
       t2.Type AS ResultType
FROM       cte t1
INNER JOIN cte t2
        ON t1.Id = t2.Id
       AND t1.highest_rank = t2.rank

Try it here.