How do I select some lines of different audiences?

Asked

Viewed 54 times

0

I need to select an audience within some constraints.

  • The first 3 lines of the GRUPO 1

  • The first line of GRUPO 3

    In case you don’t have first line on GRUPO 3:

    • The first line of GRUPO 4

    • The first line of GRUPO 2

      If you don’t have the first line of GRUPO 4
      So instead of selecting only the first line of GRUPO 2, shall select 2 lines.

I’m trying to rank and catch it with case when, but it’s complicated. Somebody help me?

Thank you.

I have a public table in the following format:

CLIENT GROUP RANK
100 1 1
101 1 2
102 1 3
103 1 4
104 1 5
105 2 1
106 2 2
107 2 3
108 2 4
109 4 1
110 4 2

Final Table Caught the top 3 of GRUPO 1, the first line of GRUPO 4 and the first of GRUPO 2

CLIENT GROUP RANK
100 1 1
101 1 2
102 1 3
109 4 1
105 2 1
sql
  • 2

    welcome Juliana. Read your question and try to think of people who do not know the tables, the fields, nothing, there is no help. Ask the query and data model you already have, and an example of the expected result

  • Would making the UNION of selects with due conditions not be a better approach than with CASE/WHEN?

  • Which one of yours SGBD? Can you put in your question the script table creation and data insertion?

2 answers

1

Interpreting "if there is no first line" as no records in this group, try:

SELECT * FROM (
(SELECT * FROM tabela WHERE GRUPO = 1 ORDER BY CLIENTE LIMIT 3) a
UNION
(SELECT * FROM tabela WHERE GRUPO = 3 ORDER BY CLIENTE LIMIT 1) b
UNION
(SELECT * FROM tabela WHERE GRUPO = 4 AND NOT EXISTS (SELECT * FROM tabela WHERE GRUPO = 3) ORDER BY CLIENTE LIMIT 1) c
UNION
(SELECT * FROM tabela WHERE GRUPO = 2 AND NOT EXISTS (SELECT * FROM tabela WHERE GRUPO = 3)ORDER BY CLIENTE LIMIT 1) d
UNION
(SELECT * FROM tabela WHERE GRUPO = 2 AND NOT EXISTS (SELECT * FROM tabela WHERE GRUPO = 4) ORDER BY CLIENTE LIMIT 1 OFFSET 1) e
)
ORDER BY CLIENTE;

1

Juliana, follows a suggestion for tests using a CTE to obtain the number of lines of the groups to then use this amount in the Where clause:

with CTE_Count as
(
    select
        count(case when Grupo = 2 then 1 end) as QtdGrupo2,
        count(case when Grupo = 3 then 1 end) as QtdGrupo3,
        count(case when Grupo = 4 then 1 end) as QtdGrupo4
    from Tabela
)

select
    t.*
from Tabela as t
cross join CTE_Count as c
where
    (t.Grupo = 1 and t.Rank <= 3) or
    (t.Grupo = 3 and t.Rank = 1) or
    (t.Grupo = 4 and t.Rank = 1 and c.QtdGrupo3 = 0) or
    (t.Grupo = 2 and 
        (
            (t.Rank = 1 and c.QtdGrupo3 = 0) or
            (t.Rank = 2 and c.QtdGrupo3 = 0 and c.QtdGrupo4 = 0)
        ) 
    )

I hope it helps

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.