Clause COUNT (1) and DENSE_RANK ()

Question

Clause COUNT (1) and DENSE_RANK ()

Navigation

#1 by (4 votes)
#2 by (3 votes)

1

I have the following query on sql server, the goal is to bring the records of the Personas table that are related to ListaMarketing_persona but since this is an AnyToAny table that joins ListaMarketing , there may be several people related to various Mark lists. my query was the following:

SELECT  
    distinc
    p.Id,
    p.Nombres,
    p.ApellidoPaterno, 
    p.ApellidoMaterno, 
    count(1) over (partition by null) TotalRegistros, 
    ROW_NUMBER() over ( ORDER BY p.FechaActualizacion DESC ) AS RowNum, 
FROM Personas as p 
    inner join Estados as e on p.EstadoId=e.Id
    inner join ListaMarketing_Persona as lmpe on p.Id=lmpe.IdPersona
    inner join ListaMarketing as lm on lmpe.IdListaMarketing=lm.Id 

where lmpe.IdListaMarketing in (select Id from @Temp_LstMark)

With distinc I can avoid duplicates, but I need the ROw_NUMBER() but this makes that happen:

ID      Nombres      ApellidoPate ApellidoMate        TotReg ROwNum
170112  Juan         Quispe       Santos              53     22
170111  Vit          Ali          Cruz                53     23
46      SARA         ABIGAILL     HUAHUAMULLO MAMANI  53     24
31      ELIZABETH    QUISPE                           53     25
28      CARLA IONEE  CABANILLAS   GALLARDO            53     26
28      CARLA IONEE  CABANILLAS   GALLARDO            53     27
27      VICTOR ELVIS ALVAREZ                          53     28

The RowNum makes the distinction not work, in the duplicate data, and the record is doubled in 28

I decided to use is DENSE_RANK() .

SELECT  
    distinc
    p.Id,
    --count(1) over (partition by null) TotalRegistros,
    DENSE_RANK() OVER (ORDER BY  p.FechaActualizacion DESC) AS RowNum,

This does not duplicate anymore. but I get this result:

index     ID    Nombres        ApePaterno   ApeMaterno    TotaReg  RowNum
47        33    ELSA           CUAYLA                     53       46 
48        30    JIMY JUAN      CASTILLO     QUISPE        53       47
49        26    ODALIZ SHARON  ALVAREZ      ARREDONDO     53       48

You can see all the records that are a total of 49 , but the total number of records keeps coming 53

sql sql-server

asked by Vitmar Aliaga 18.11.2016 в 19:55

source

2 answers

3

Due to these limitations:

You can not use a window function as an argument to another window function
You can not use distinct within count if you have a clause over

The only way I can think of to achieve this is using a CTE: First you calculate the dense_rank () and then you get the max () of said dense_rank, something in the line of:

with 
t1 as (
          select '1a' id1
union all select '1b'
union all select '1c'
union all select '1d'
union all select '1e'
union all select '1f'
union all select '1g'
)
,
t2 as (
          select '2m' id2, '1a' id1
union all select '2n', '1a'
union all select '2o', '1b'
union all select '2p', '1c'
union all select '2q', '1d'
union all select '2r', '1e'
union all select '2s', '1e'
union all select '2t', '1f'
union all select '2u', '1g'
)
,
CalcRank as (
select   t1.id1
       , t2.id2
       , dense_rank() over (order by t1.id1) the_rank
  from t1 
       inner join t2 on t2.id1 = t1.id1
)
select a.*
       , max(the_rank) over () max_rank 
  from CalcRank a

The result of this query is:

id1  id2  the_rank             max_rank
---- ---- -------------------- --------------------
1a   2n   1                    7
1a   2m   1                    7
1b   2o   2                    7
1c   2p   3                    7
1d   2q   4                    7
1e   2s   5                    7
1e   2r   5                    7
1f   2t   6                    7
1g   2u   7                    7

(9 row(s) affected)

As you can see, we get nine records, the rank reaches up to 7 and max_rank returns us correctly 7.

answered by 18.11.2016 в 21:38

variable in mysqli_query Center span with image inside a column td

score 4 · Accepted Answer

Based on the current status of your question, I would do the consultation in 2 stages:

Retrieve the list of id of the people that interest me filtering the duplicates:

select distinct IdPersona
  from ListaMarketing_Persona
 where IdListaMarketing in (select Id from @Temp_LstMark)

Make a join with Personas for the final result:

;with cte as (
  select distinct IdPersona
    from ListaMarketing_Persona
   where IdListaMarketing in (select Id from @Temp_LstMark)
)
select p.Id,
       p.Nombres,
       p.ApellidoPaterno,
       p.ApellidoMaterno,
       count(*) over () as TotalRegistros,
       row_number() over (order by p.FechaActualizacion desc) as RowNum
  from cte c
  join Personas p
    on p.Id = c.IdPersona

Because the first stage filters the duplicates, you can still use row_number normally.

Since I do not see the use of Estados and ListaMarketing in your query, I removed them. If you really need them, then it means that you lack information relevant to your question.