0

i have two joined table in my postgres db

tb_items and tb_colors

id_item | group |id colors
--------------------------
1        a         1 
2        b         1
3        b         2
4        c         4
5        c         5

id_color | color| subcolor
---------------------------------
1          black        var1  
2          red          type1
3          red          type2
4          blue         aaa1
5          blue         xxx3
6          red          hex1 

that mean in my query i have

id_item | group | color | subcolor
----------------------------------
1         a       black    var1
2         b       red      type1
3         b       red      type2
4         c       blue     aa1
5         c       red      hex1 

basically what i want is renaming color name based on numers of color item in a groups or

 id_item | group | color 
----------------------------------
 1         a       black
 2         b       red
 3         b       red-1
 4         c       blue
 5         c       red

i dont' really know whato to do..

1
WITH 
cte1 AS ( SELECT t1.id_item, 
                 t1."group",
                 t2.id_color,
                 t2.color,
                 t2.subcolor
          FROM tb_items t1
          NATURAL JOIN tb_colors t2 ),
cte2 AS ( SELECT color, COUNT(DISTINCT subcolor) subcolors
          FROM cte1
          GROUP BY color )
SELECT cte1.id_item, 
       cte1."group",
       CASE cte2.subcolors 
       WHEN 1 THEN cte1.color
              ELSE cte1.color || '-' || ROW_NUMBER() OVER (PARTITION BY cte1.color
                                                           ORDER BY tb_colors.subcolor)
       END color
FROM tb_colors
NATURAL JOIN cte1
NATURAL JOIN cte2
ORDER BY cte1.id_item;

fiddle

| improve this answer | |
0

A slight variation of Akinas solution:

select id_item
     , color || case when max(rn) over (partition by "group") = 1 then '' else '-' || rn end
from (
    select a.id_item, a."group", b.color
         , row_number() over (partition by a.group order by b.id_color) as rn
    from tb_items a
    natural join tb_colors b
) as t

His solution is a bit easier to grasp, so stick with that. Just added this as a alternative way of doing it.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.