Home / Questions / 20958
Accepted
El Cóndor
Asked: 2020-08-19 04:37:14 +0800 CST 2020-08-19 04:37:14 +0800 CST

How to select only the first records of a ranking when there are several ranking groups from a stored procedure in MySQL?

  • 772

I have the following stored procedure in MySQL, it works for me but nevertheless it brings me more records, I mean that there are several records that coincide in almost all the fields except in the series and in the date, for that reason I decided to order them as in a ranking .

CREATE DEFINER=`usuarioroot`@`%` PROCEDURE `spRank`()
BEGIN

set @report_rank := 0;
set @current  := 0; 

select a.*, 
       @report_rank := IF(@current  = clave , @report_rank + 1, 1) AS report_rank,
                                            @current  := clave              
  from (
    SELECT tabla1.serie,Count(*) as cantSerie,fecha,  
        CASE WHEN DateDiff(fecha,Now())<0 Then 0 Else  
        CASE WHEN DateDiff(fecha,Now())>0 AND DateDiff(fecha,Now())<20 Then 1 
                        Else 2 End End As Codfecha, 
                        DateDiff(Max(fecha),Now()) as DiasExp, 
                        CASE WHEN IsNull(seriePdf) Then 'No' Else 'Si' end as EnPdf,  
                        tabla2.clave,campo4 as antiguo 
                        From bd1.tabla1, bd1.tabla2, bd2.tabla3  ,
                            (SELECT tabla2.clave 
                                From bd1.tabla2, bd2.tabla3 
                                Where tabla2.clave = bd2.tabla3.clave 
                                Group By tabla2.clave) 
                                Subclave
                                Where tabla2.clave = bd2.tabla3.clave  
                                AND tabla1.serie=tabla2.serie  
                                and subclave.clave = tabla2.clave
                                GROUP BY tabla2.clave,tabla1.serie 
                                ) a
            ORDER BY antiguo,clave,fecha DESC,cantSerie,serie;

END

I can get the following result:

  serie    cantSerie         fecha       Codfecha   DiasExp  EnPdf  clave  antiguo report_rank @current :=clave

123AB0123      5     2016-04-26 00:00:00     0       -114      Si   01A1     NO        1                01A1
123AC0123      4     2014-03-26 00:00:00     0       -876      Si   01A1     NO        2                01A1
123BC0123      1     2013-03-30 00:00:00     0       -1237     Si   01A1     NO        3                01A1
123BD0123      5     2013-03-30 00:00:00     0       -1237     Si   01A1     NO        4                01A1
124BF0123      1     2016-06-10 00:00:00     0       -69       Si   01A2     NO        1                01A2
123BD0023      1     2013-03-30 00:00:00     0       -1237     Si   01A2     NO        2                01A2

But I only want the records that have report_rank = 1, I tried using the where clause like this: where @report_rank = 1 but it doesn't bring me any record and it doesn't give me an error, I would also like the last 2 fields not to be seen in the final result :report_rank @current :=clave

In a nutshell I want to get the following result:

  serie    cantSerie         fecha       Codfecha   DiasExp  EnPdf  clave  antiguo 

123AB0123      5     2016-04-26 00:00:00     0       -114      Si   01A1     NO        
124BF0123      1     2016-06-10 00:00:00     0       -69       Si   01A2     NO
mysql

1 Answers

  1. Best Answer

    Since you are trying to filter based on an inferred column, it is not possible to filter using WHEREwhich a priori filters the results based on the rows. This is why there is the keyword HAVINGwhich filters a posteriori, that is, it filters based on the results already obtained.

    Your query would look like this:

    select a.*, 
       @report_rank := IF(@current  = clave , @report_rank + 1, 1) AS report_rank,
                                            @current  := clave              
  from (
    SELECT tabla1.serie,Count(*) as cantSerie,fecha,  
        CASE WHEN DateDiff(fecha,Now())<0 Then 0 Else  
        CASE WHEN DateDiff(fecha,Now())>0 AND DateDiff(fecha,Now())<20 Then 1 
                        Else 2 End End As Codfecha, 
                        DateDiff(Max(fecha),Now()) as DiasExp, 
                        CASE WHEN IsNull(seriePdf) Then 'No' Else 'Si' end as EnPdf,  
                        tabla2.clave,campo4 as antiguo 
                        From bd1.tabla1, bd1.tabla2, bd2.tabla3  ,
                            (SELECT tabla2.clave 
                                From bd1.tabla2, bd2.tabla3 
                                Where tabla2.clave = bd2.tabla3.clave 
                                Group By tabla2.clave) 
                                Subclave
                                Where tabla2.clave = bd2.tabla3.clave  
                                AND tabla1.serie=tabla2.serie  
                                and subclave.clave = tabla2.clave
                                GROUP BY tabla2.clave,tabla1.serie 
                                ) a
       HAVING report_rank = 1 -- <== filtra a posteriori
            ORDER BY antiguo,clave,fecha DESC,cantSerie,serie;

    Another possible option would have been to calculate this value inside the WHERE, but this makes it less than optimal, since it would be calculating this for each row, possibly doubling the response time.

    • 1